Saturday, January 19, 2008

find all tables with column name *

Applies to: SQL
Tested on: SQL 2005
Keywords: SQL, column, all, tables

I'm going to need this quite often...

just execute these queries on the same database where the tables reside

--get all table names for a specific column name
SELECT o.name as TableName
FROM sysobjects o inner join syscolumns c on o.id=c.id
WHERE c.name = 'YOURCOLUMNNAMEHERE'

you can also perform like queries

--get all columns and table names for a 'like column' query
SELECT c.[name] as ColumnName, o.name as TableName FROM sysobjects o inner join syscolumns c on o.id=c.id
WHERE c.name like 'COLUMN%'

No comments: