Find all Tables with Specified Column Name in a SQL Server Database

Here is a simple query you can use to find all tables and views that have a column with a specified column name in a SQL Server database:

SELECT c.TABLE_CATALOG, c.TABLE_SCHEMA, c.TABLE_NAME ,t.TABLE_TYPE, c.COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS c
LEFT OUTER JOIN INFORMATION_SCHEMA.TABLES t ON c.TABLE_CATALOG = t.TABLE_CATALOG
AND c.TABLE_SCHEMA = t.TABLE_SCHEMA
AND c.TABLE_NAME = t.TABLE_NAME
WHERE c.COLUMN_NAME like '%ColumnName%'

To find only tables with specified column name in a database:

SELECT c.TABLE_CATALOG, c.TABLE_SCHEMA, c.TABLE_NAME ,t.TABLE_TYPE, c.COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS c
LEFT OUTER JOIN INFORMATION_SCHEMA.TABLES t ON c.TABLE_CATALOG = t.TABLE_CATALOG
AND c.TABLE_SCHEMA = t.TABLE_SCHEMA
AND c.TABLE_NAME = t.TABLE_NAME
WHERE c.COLUMN_NAME like '%Product%'
AND t.TABLE_TYPE = 'BASE TABLE'

To find only views with specified column name in a database:

SELECT c.TABLE_CATALOG, c.TABLE_SCHEMA, c.TABLE_NAME ,t.TABLE_TYPE, c.COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS c
LEFT OUTER JOIN INFORMATION_SCHEMA.TABLES t ON c.TABLE_CATALOG = t.TABLE_CATALOG
AND c.TABLE_SCHEMA = t.TABLE_SCHEMA
AND c.TABLE_NAME = t.TABLE_NAME
WHERE c.COLUMN_NAME like '%Product%'
AND t.TABLE_TYPE = 'VIEW'

The same can be achieved by querying sys.views, sys.tables and sys.columns:

To find all tables and views with specified column name in a database:

SELECT c.name AS 'Column_Name'
,sc.name AS Schema_Name
,t.name AS 'Object_Name'
,t.type_desc AS Object_Type
FROM sys.columns c
JOIN sys.tables t ON c.object_id = t.object_id
JOIN sys.schemas sc ON t.schema_id = sc.schema_id
WHERE c.name LIKE '%ColumnName%'
UNION
SELECT c.name AS 'Column_Name'
,sc.name AS Schema_Name
,v.name AS 'Object_Name'
,v.type_desc AS Object_Type
FROM sys.columns c
JOIN sys.views v ON c.object_id = v.object_id
JOIN sys.schemas sc ON v.schema_id = sc.schema_id
WHERE c.name LIKE '%ColumnName%'
ORDER BY Object_Name, Column_Name;

To find only tables with specified column name in a database:

SELECT c.name AS 'Column_Name'
,sc.name AS Schema_Name
,t.name AS 'Table_Name'
,t.type_desc AS Object_Type
FROM sys.columns c
JOIN sys.tables t ON c.object_id = t.object_id
JOIN sys.schemas sc ON t.schema_id = sc.schema_id
WHERE c.name LIKE '%ColumnName%'
ORDER BY Table_Name, Column_Name;

To find only views with specified column name in a database:

SELECT c.name AS 'Column_Name'
,sc.name AS Schema_Name
,v.name AS 'View_Name'
,v.type_desc AS Object_Type
FROM sys.columns c
JOIN sys.views v ON c.object_id = v.object_id
JOIN sys.schemas sc ON v.schema_id = sc.schema_id
WHERE c.name LIKE '%ColumnName%'
ORDER BY View_Name, Column_Name;

If the post helped you, please share it:
Pin It

Leave a Reply

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">