Submit My Question - Recent Questions - Recent Answer
Msdn Tech >> SQL Server >> Transact-SQL

find out what tables contain a specific column

I want to write SQL that will search the tables in a database for a specific column, like this. For instance, I have a column "Unique_ID" that is in many of our tables (hundreds) but not in others and want to find out the tables it is in. It is always the first column.

I tried to find a system stored procdure to do this but couldn't and tried to create a script using the sysobjects and syscolumns tables in the Master db, but came to a roadblock because they don't seem to be related at all.

I would surely appreciate if someone else has already done this!

Thanks!

[604 byte] By [mz1derful] at [2007-12-22]
«« Xml schema features in DataContract/DataMember
»» AssemblyInfoTask weird behavior
# 1

When I use the sysobjects/syscolumns to get info about a db I always do it in the DB I need info about, not in the master table.

use MyDatabase;
select * from sysobjects where name like 'someprefix%'

To get some info it works well but not recommended to use in production.

AndreasJohansson at 2007-8-30 > top of Msdn Tech,SQL Server,Transact-SQL...
# 2

SELECT a.TABLE_NAME, b.COLUMN_NAME, b.ORDINAL_POSITION

FROM INFORMATION_SCHEMA.TABLES a JOIN INFORMATION_SCHEMA.COLUMNS b

ON a.TABLE_NAME = b.TABLE_NAME AND a.TABLE_NAME LIKE 'TblName%'

AND b.COLUMN_NAME LIKE 'ColName%'

ORDER BY a.TABLE_NAME

YogeshRanade at 2007-8-30 > top of Msdn Tech,SQL Server,Transact-SQL...
# 3
Nope, this one yielded no results. I'll try the next one and let you all know. Thanks for trying!
mz1derful at 2007-8-30 > top of Msdn Tech,SQL Server,Transact-SQL...
# 4
WONDERFUL! Just what I was looking for. Thank you so much!
mz1derful at 2007-8-30 > top of Msdn Tech,SQL Server,Transact-SQL...
# 5

Try this way. If you still don't get anything, then you have other "issues".

SELECT a.TABLE_NAME, b.COLUMN_NAME, b.ORDINAL_POSITION
FROM INFORMATION_SCHEMA.TABLES a JOIN INFORMATION_SCHEMA.COLUMNS b
ON a.TABLE_NAME = b.TABLE_NAME
ORDER BY a.TABLE_NAME

Aego at 2007-8-30 > top of Msdn Tech,SQL Server,Transact-SQL...
SQL Server
Transact-SQL

SQL Server Hot Topic

SQL Server New Topic

SQL Server

Java / MSDN / Linux Tech / PHP Tech / MSDN DotNet / MSDN TECH / Health Library / Programmers Tech / visual basic Tech / Developer Tech / Ipod faqs / Msdn China Tech

Site Classified