Find table by column name

Question

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.

Anil Kumar · Answer

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

Kunal Vaishya · Answer

Dear Try This ItRate Is a Column 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')

Kunal Vaishya · Answer

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 Draskovic · Answer

Thanks this one works and I managed to get results with Kunal's answer so thank you both

Neven Draskovic · Answer

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?