Find all Tables that contain a specific Column name in SQL Server
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_CATALOG | TABLE_SCHEMA | TABLE_NAME | COLUMN_NAME | org_pos | DATA_TYPE | CML |
---|---|---|---|---|---|---|
SQL_DBA | dbo | tbl_Errors_Stats | Errodate | 6 | datetime | NULL |
SQL_DBA | dbo | tbl_Errors_Stats | ErrorMsg | 5 | varchar | -1 |
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 Name | Table Name |
---|---|
DatabaseName | tbl_Errors_Stats |
SQLInstanceName | tbl_Errors_Stats |
StatisticsName | tbl_Errors_Stats |
DatabaseName | tbl_QualifiedDBs |
DatabaseName | tbl_Statistics_Update_Trans |
SQLInstanceName | tbl_Statistics_Update_Trans |
StatisticsName | tbl_Statistics_Update_Trans |
TableName | tbl_Statistics_Update_Trans |
DatabaseName | tbl_Statistics_Update_Trans_Post |
SQLInstanceName | tbl_Statistics_Update_Trans_Post |
StatisticsName | tbl_Statistics_Update_Trans_Post |
TableName | tbl_Statistics_Update_Trans_Post |
Please Login to comment...