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!

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.

|||

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

|||Nope, this one yielded no results. I'll try the next one and let you all know. Thanks for trying!|||WONDERFUL! Just what I was looking for. Thank you so much!|||

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