Find table by column name

Neven Draskovic

14y3kSQL

Hey
I have to use a pretty large database, that I don't have complete documentation for. My question is how can I find the table that contains a certain column (I know the columns name)? I work in VS 2010 and I have access to SQL Server Management Studio.

5 Replies

Know the answer? Post it — somebody with the same question will find it here.

Kunal Vaishya14y ago
DECLARE @SQL VARCHAR(max)
SELECT @SQL = ''
SELECT @SQL = @SQL + 'UNION
SELECT ''' + name + ''' [DB], t.name [TBL], c.name [COL] FROM ' + name + '..sysobjects t INNER JOIN ' + name + '..syscolumns c ON t.id = c.id where c.name like ''ItRate%'' '
FROM master..sysdatabases
WHERE name not IN ('master', 'msdb', 'tempdb','model')
SELECT @SQL = RIGHT(@SQL, LEN(@SQL)-5) + ' ORDER BY 1, 2, 3'
Print @SQL
EXEC(@SQL)
Neven Draskovic14y ago
Thanks, this one works, and I managed to get results with Kunal's answer so thank you both
Anil Kumar14y ago
Try

SELECT Table_Schema, Table_Name, Column_Name, Data_Type
FROM information_schema.columns
WHERE column_name like '%myColumnName%'
ORDER By table_schema, table_name
Neven Draskovic14y ago
Unfortunately this doesn't work. I tried something similar, and now your code, but it only displays results for one database (not the on I need) while there is almost 20 databases on that server. How do I specify the database in which I want to find the column?
Kunal Vaishya14y ago
Dear Try This

ItRate Is a Co,lumn Name Put Here Column Name and You can FInde Table Name

Select T.Name From Sys.Tables AS T Where Object_Id In (Select Object_Id From Sys.Columns As C Where C.Name = 'ItRate')