How to Compare two tables for Column Names in SQL Server - SQL Server / TSQL Tutorial

Scenario:

We as developer often need to find if two tables has the same number of columns and the columns matched. We like to see information

List of Columns match in both table
Column/s which does not exists in Table1 or Source Table
Column/s which does not exists in Table2 or Destination Table


Solution:


We can use below query. I have created couple of sample tables so you can see what our query will return.

CREATE TABLE dbo.Table1 (
    id INT
    ,fname VARCHAR(100)
    ,lname VARCHAR(100)
    ,address VARCHAR(50)
    )
GO

CREATE TABLE dbo.Table2 (
    id INT
    ,NAME VARCHAR(100)
    ,address VARCHAR(50)
    )


Let's run below query after providing the Schema Names and Table Names. 
--Provide the Schema and Table Names for comparison
DECLARE @Table1Schema VARCHAR(50) = 'dbo'
DECLARE @Table2Schema VARCHAR(50) = 'dbo'
DECLARE @Table1Name VARCHAR(50) = 'Table1'
DECLARE @Table2Name VARCHAR(50) = 'table2'

;

WITH CTE1
AS (
    SELECT *
    FROM information_schema.columns
    WHERE table_schema = @Table1Schema
        AND table_name = @Table1Name
    )
    ,CTE2
AS (
    SELECT *
    FROM information_schema.columns
    WHERE table_schema = @Table2Schema
        AND table_name = @Table2Name
    )
SELECT
    --cte1.Table_Schema,cte1.Table_Name,cte1.Column_Name,
    --cte2.Table_Schema,cte2.Column_Name,cte2.Table_Name,
    IsNull(cte1.Column_Name, cte2.Column_Name) AS ColumnName
    ,CASE 
        WHEN cte1.Column_Name = cte2.Column_Name
            THEN 'Exists in Both Tables ( ' + @Table1Name + ' , ' + @Table2Name + ' )'
        WHEN cte1.Column_Name IS NULL
            THEN 'Does not Exists in ' + @Table1Name
        WHEN cte2.Column_Name IS NULL
            THEN 'Does not Exists in ' + @Table2Name
        END AS IsMatched
FROM CTE1
FULL JOIN cte2 ON cte1.Column_Name = cte2.Column_Name

Output for comparison of two tables columns
How to compare two tables columns in SQL server to find matching, Not matching Columns - SQL Server Tutorial / TSQL Tutorial

2 comments:

  1. At the same time, this doesn't mean that one will need to continually change absolutely everything that they believe. Just as the chassis of a car can stay the same with only certain features changing each time a new version is released; one can have certain views that don't change

    Sarah Harris

    ReplyDelete
  2. Efficiently written information. It will be profitable to anybody who utilizes it, counting me. Keep up the good work. For certain I will review out more posts day in and day out. Car Insurance Comparison Website

    ReplyDelete