How to find the SQL table name given column names in SQL Server 2012

Question

I have a large database with over 100 tables running on MS SQL Server 2012.

I have a series of column names in an output (Excel file) which was created in the past by another person.

How can I find to which SQL tables the column names belong to?

Answer 1

Use this

SELECT  distinct table_name FROM INFORMATION_SCHEMA.COLUMNS
WHERE column_name='your_column_name'

Answer 2

You can use the below query if you have the column name you are looking for.

SELECT sc.name,so.name FROM syscolumns sc JOIN 
 sys.tables st ON sc.id = st.object_id WHERE st.type = 'U' and sc.name = 'Your_column_name'

Of course there are additional filters that you can explore on these tables to filter the type of objects down.

Answer 3

If you need to get current list of tables/cols and then compare with old document:

SELECT 
     [st].[name]    AS "@name"
    ,[sc].[columns] AS "*"    
FROM 
    [sys].[tables] AS [st] 
OUTER APPLY
    (
        SELECT [columns] = CAST((SELECT [name] as "column/@name" FROM [sys].[columns] WHERE [object_id] = [st].[object_id] FOR XML PATH('') ) AS XML)
    ) AS [sc]
WHERE  [st].[type] = 'U'            
FOR XML PATH('table'), ROOT('root');