SQL Server: Tips and Tricks - 2 (2017)

How to compare two tables for Column Names in SQL Server

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

https://4.bp.blogspot.com/-d1QeUMxDcpg/Vt9TOfRFZuI/AAAAAAAAGzg/xoT4g4bmIwo/s640/Capture.PNG