How to compare Columns of two tables in SQL Server

By | November 6, 2020

Background / Problem Statement –

Many times while working on projects, we need to compare columns of two tables in sql server and find out what are matching columns and what are different columns in both the tables.

Solution – 

lets say we have below two tables,

CREATE TABLE dbo.Employee (
     EmployeeId INT
    ,fullName VARCHAR(100)
    ,address VARCHAR(50)
    ,mobileNumber varchar(9)
    )
GO

CREATE TABLE dbo.Customer (
     customerId INT
    ,fullName VARCHAR(100)
    ,address VARCHAR(50)
    ,phoneNumber varchar(9)
    )
GO

Now read all the columns of both the tables using CTE – Common table expression,

;WITH CTEEmployee
AS (
    SELECT * FROM 
      information_schema.columns 
    WHERE 
      table_schema = 'dbo' 
    AND 
      table_name = 'Employee'
    )
    ,CTECustomer
AS (
    SELECT * FROM 
    information_schema.columns
    WHERE 
    table_schema = 'dbo'
    AND 
    table_name = 'Customer'
    )
SELECT
    IsNull(CTEEmployee.Column_Name, CTECustomer.Column_Name) AS ColumnName
    ,CASE 
        WHEN CTEEmployee.Column_Name = CTECustomer.Column_Name
            THEN 'Present in Both the tables ( ' + 'Employee' + ' , ' + 'Customer' + ' )'
        WHEN CTEEmployee.Column_Name IS NULL
            THEN 'Not exists in ' + 'Employee'
        WHEN CTECustomer.Column_Name IS NULL
            THEN 'Not exists in ' + 'Customer'
        END AS IsMatched
FROM CTEEmployee
FULL JOIN CTECustomer ON CTEEmployee.Column_Name = CTECustomer.Column_Name

Result as below,

How to compare tables in SQL Server - TSQL

How to compare columns of two tables in SQL Server – TSQL