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 example. For this SQL Interview Question, We are using the SQL Tutorial database. The below screenshot will show you the tables inside that database.

Find all Tables that Contain Specific Column Name 1

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

-- Query to Find all Tables that Contain Column Name
USE [SQL Tutorial]
GO
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 that Contain Specific Column Name 2

Find all Tables with Specific Column Name

In this Interview Question example, we are using the sys.column to get the column information, and sys.tables to get the database table names.

-- Query to Find all Tables that Contain Column Name
USE [SQL Tutorial]
GO

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]
Find all Tables that Contain Specific Column Name 3