I want to show all tables that have specified column name

Solution 1:

Pretty simple on a per database level

Use DatabaseName
Select * From INFORMATION_SCHEMA.COLUMNS Where column_name = 'ColName'

Solution 2:

select table_name
from information_schema.columns
where COLUMN_NAME = 'MyColumn'

Solution 3:

You can use the information schema views:

SELECT DISTINCT TABLE_SCHEMA, TABLE_NAME
FROM Information_Schema.Columns
WHERE COLUMN_NAME = 'ID'

Here's the MSDN reference for the "Columns" view: http://msdn.microsoft.com/en-us/library/ms188348.aspx

Solution 4:

If you're trying to query an Oracle database, you might want to use

select owner, table_name 
from all_tab_columns
where column_name = 'ColName';