Find all tables containing column with specified name - MS SQL Server Find all tables containing column with specified name - MS SQL Server sql-server sql-server

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).