Cry How to...

List all tables that contain a given column name

To list all the tables in an SQL Server database that contain a given column name, use the following SQL:

select sysobjects.name, * from syscolumns, sysobjects
where syscolumns.name='MyColumn'
and sysobjects.id = syscolumns.id
and (sysobjects.xtype='U' or sysobjects.xtype='S')

Note:

• Replace 'MyColumn' with the name of the column you wish to search on.
• This query will return the names of tables that contain the specified column for the current database only.

These notes have been tested against SQL Server 7 and SQL Server 2000.