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 –