Find all Tables that contain a specific Column name in SQL Server
Last Updated :
21 Jan, 2021
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 |
Like Article
Suggest improvement
Share your thoughts in the comments
Please Login to comment...