How to find all tables that have specified column name in Sql Server?

We can use a script like below to find all the tables in the database that have column with specified name in it:

SELECT SCHEMA_NAME(schema_id) + '.' + t.name AS 'Table Name'
FROM sys.tables t 
     INNER JOIN sys.columns c
        ON c.object_id = t.object_id
WHERE c.name like '%ColumnName%'
ORDER BY 'Table Name'

Let us understand this with an example. In the below example, creating a demo database SQLHINTSDEMO with three tables Customer, Orders and Employee. And then search all the tables in the SQLHINTSDEMO database which have column name like CustomerId. It should return two table names Customer and Orders as both have column with column name like CustomerId.

CREATE DATABASE SQLHINTSDEMO
GO
USE SQLHINTSDEMO
GO
CREATE TABLE dbo.Customer(CustomerId INT, 
 CustomerName VARCHAR(100))
GO
CREATE TABLE dbo.Orders(OrderId INT,CustomerId INT)
GO
CREATE TABLE dbo.Employee(EmployeeId INT, 
 EmployeeName VARCHAR(100))
GO
SELECT SCHEMA_NAME(schema_id) + '.' + t.name AS 'Table Name'
FROM sys.tables t 
 INNER JOIN sys.columns c
 ON c.object_id = t.object_id
WHERE c.name like '%CustomerId%'
ORDER BY 'Table Name'
GO

RESULT

Find all tables that have specified column name in Sql Server

3 thoughts on “How to find all tables that have specified column name in Sql Server?

  1. “How to find all tables that have specified column name in Sql Server?
    | SqlHints.com” was a superb post, can’t help but wait
    to look over much more of your blog posts. Time to waste some time on-line lolz.
    Thanks a lot ,Clarissa

Leave a Reply

Your email address will not be published.