Find all Tables that contain a specific Column name in SQL Server
When managing a database in SQL Server, it’s often necessary to find out which tables include a specific column name. This task is essential for database administrators and developers, especially when updating database structures, migrating data safely, or getting a better understanding of how information is organized.
There are two ways to find all tables that contain a specific column name in SQL Server:
- Using sys.column and sys.table view
- Using INFORMATION_SCHEMA.COLUMNS View
SQL_DBA
Using sys.column and sys.table view
Using sys.columns and sys.tables views, users can find all tables that contain a specific column name.
We can use sys.column view to get the column information, and sys.tables view to get the database table names. Using JOIN we can merge the results of these views.
Syntax
Syntax to find tables that contain a specific column name using sys.column and sys.table views.
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 |
Using INFORMATION_SCHEMA.COLUMNS View
Using INFORMATION_SCHEMA.COLUMNS view, you can find all tables that contain a specific column name in the SQL database.
We can use the INFORMATION_SCHEMA.COLUMNS to get the table names where the column name is like ‘%Err%’.
Syntax
Syntax to find tables that contain a specific column name using INFORMATION_SCHEMA.COLUMNS views.
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 |