Find all Tables that Contain Specific Column Name in SQL

In this article, we will show how to write an 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 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 Tables with Specific Column Names

In this example, we are using the sys.column to get the column information and sys.tables to get the database table names. Please refer to the Find all Dependencies on Table and SQL database articles from the SQL tutorial page.

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]