Find all Tables that Contain Specific Column Name

In this article, we will show how to write a SQL Query to Find all Tables that Contain Specific Column Name with an example. For this example, we are using the following database. The below screenshot will show you the tables inside that database.

Find all Tables that Contain Specific Column Name in Sql Server

In this SQL Server example, we are using INFORMATION_SCHEMA.COLUMNS to get the table names where the column name is equal to Education

SELECT TABLE_CATALOG, 
       TABLE_SCHEMA, 
       TABLE_NAME, 
       COLUMN_NAME, 
       ORDINAL_POSITION, 
       DATA_TYPE, 
       CHARACTER_MAXIMUM_LENGTH
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE COLUMN_NAME like '%Education%' 
ORDER BY TABLE_NAME

Find all Tables with 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.

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 '%Education%'
ORDER BY [Table Name],
         [Column Name]