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 - 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 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 |
Attention reader! Don’t stop learning now. Get hold of all the important DSA concepts with the DSA Self Paced Course at a student-friendly price and become industry ready.