Find all tables containing column with specified name - MS SQL Server
Search Tables:
SELECT c.name AS 'ColumnName' ,t.name AS 'TableName'FROM sys.columns cJOIN sys.tables t ON c.object_id = t.object_idWHERE c.name LIKE '%MyName%'ORDER BY TableName ,ColumnName;
Search Tables and Views:
SELECT COLUMN_NAME AS 'ColumnName' ,TABLE_NAME AS 'TableName'FROM INFORMATION_SCHEMA.COLUMNSWHERE COLUMN_NAME LIKE '%MyName%'ORDER BY TableName ,ColumnName;
We can also use the following syntax:-
select * from INFORMATION_SCHEMA.COLUMNS where COLUMN_NAME like '%clientid%' order by TABLE_NAME
SQL Server:
SELECT Table_Name, Column_Name FROM INFORMATION_SCHEMA.COLUMNSWHERE TABLE_CATALOG = 'YOUR_DATABASE'AND COLUMN_NAME LIKE '%YOUR_COLUMN%'
Oracle:
SELECT owner, table_name, column_name FROM all_tab_columns WHERE column_name LIKE '%YOUR_COLUMN_NAME%'AND OWNER IN ('YOUR_SCHEMA_NAME');
- SIMPLE AS THAT!! (SQL, PL/SQL)
I use it ALL the time to find ALL instances of a column name in a given database (schema).