Brian Cryer

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.

About the author: is a dedicated software developer and webmaster. For his day job he develops websites and desktop applications as well as providing IT services. He moonlights as a technical author and consultant.