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 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')

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?

Find table by column name

Answers (5)