Skip to content
Related Articles
Open in App
Not now

Related Articles

Find all Tables that contain a specific Column name in SQL Server

Improve Article
Save Article
Like Article
  • Last Updated : 21 Jan, 2021
Improve Article
Save Article
Like Article

As a SQL DBA, we might need to write a SQL Query to Find all Tables that Contain Specific Column Name with example.
Below screenshot will show you the tables inside the database ‘SQL_DBA



  • Find all tables that contain a specific column name in SQL Database :
    In the below example, we are using INFORMATION_SCHEMA.COLUMNS to get the table names where the column name is like ‘%Err%’.
  • Query –

    SELECT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, 
    ORDINAL_POSITION as org_pos, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH as CML
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE COLUMN_NAME like '%Err%'
    ORDER BY TABLE_NAME

    Output –

    TABLE_CATALOGTABLE_SCHEMATABLE_NAMECOLUMN_NAMEorg_posDATA_TYPECML
    SQL_DBAdbotbl_Errors_StatsErrodate6datetimeNULL
    SQL_DBAdbotbl_Errors_StatsErrorMsg5varchar-1


  • Find all tables that contain a specific column name :
    In this example, we are using the sys.column to get the column information, and sys.tables to get the database table names.

Query –

SELECT col.name AS [Column Name], tab.name AS [Table Name]
FROM sys.columns col
INNER JOIN sys.tables tab
ON col.object_id = tab.object_id
WHERE col.name LIKE '%Name%'
ORDER BY [Table Name], [Column Name]

Output –

Column NameTable Name
DatabaseNametbl_Errors_Stats
SQLInstanceNametbl_Errors_Stats
StatisticsNametbl_Errors_Stats
DatabaseNametbl_QualifiedDBs
DatabaseNametbl_Statistics_Update_Trans
SQLInstanceNametbl_Statistics_Update_Trans
StatisticsNametbl_Statistics_Update_Trans
TableNametbl_Statistics_Update_Trans
DatabaseNametbl_Statistics_Update_Trans_Post
SQLInstanceNametbl_Statistics_Update_Trans_Post
StatisticsNametbl_Statistics_Update_Trans_Post
TableNametbl_Statistics_Update_Trans_Post
My Personal Notes arrow_drop_up
Like Article
Save Article
Related Articles

Start Your Coding Journey Now!