Show All Tables Containing A Certain Column

Question

I'm using SQL Server 2012 and was wondering if there is a way to find all tables in a given database that contain a certain column?

Answer 1

select
    quotename(S.name) + '.' + quotename(T.name) as [Table]
from sys.columns C
    join sys.tables T on T.object_id = C.object_id
    join sys.schemas S on S.schema_id = T.schema_id
where C.name = 'ColumnName'
order by 1

Answer 2

I think the easiest way is to use the INFORMATION_SCHEMA.COLUMNS table:

select c.SCHEMA_NAME, c.TABLE_NAME
from INFORMATION_SCHEMA.COLUMNS c
where c.COLUMN_NAME = @YOURCOLUMNNAME;