Thursday, December 15, 2011

Finding table by column name in a database

From time to time I need to find a table in a database which I know the column name but I have to search for the table.

Here's how to do it in Oracle:
SELECT TABLE_NAME, COLUMN_NAME
FROM USER_TAB_COLUMNS -- COLS,
WHERE COLUMN_NAME = 'ColumnNameHere'
GROUP BY TABLE_NAME, COLUMN_NAME;

Here's how to do it in SQL Server:
SELECT obj.Name, col.Name
FROM sysobjects obj, syscolumns col
WHERE obj.id = col.id
AND col.Name = 'ColumnNameHere'