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

My Personal Notes arrow_drop_up

Save