SQL – Table comparison

For table comparison, i like to use Red Gate SQL compare. Great tool, but if you don’t have money for it, you can always try to write your own comparation script.
You can then script it as function, and use it. It does not have, options like SQL Compare, but for now it will do the trick.
This simple comparation, uses only names of columns to compare. But you can code it to check also field type and field length.

First of all, lets create two simple tables:

CREATE TABLE [dbo].[Table_One](
    [Column1] [nchar](10) NULL,
    [Column2] [varchar](50) NULL,
    [Column3] [int] NULL,
    [Coulmn4] [datetime] NULL,
    [Column10] [char](20) NULL
) ON [PRIMARY]

CREATE SCHEMA [test] AUTHORIZATION [dbo]
GO
CREATE TABLE [test].[Table_Two](
    [Column1] [nchar](10) NULL,
    [Column7] [int] NULL,
    [Column8] [int] NULL,
    [Coulmn9] [datetime] NULL,
    [Column10] [varchar](20) NULL
) ON [PRIMARY]

As you see, I left

Then, declare variables for table names. It will be easier to handle table names in one place.
DECLARE @T1 VARCHAR(30) = ‘Table_One’, –names without schemas
        @T2 VARCHAR(30) = ‘Table_Two’

 

Now, we just have to create comparison select. Selecting distinct values from information_schema view first for one table, than the other.

— In TableOne but not in TableTwo
SELECT DISTINCT
       @T1 AS [First table],
       ‘>>’ AS Dir, –Direction
       @T2 AS [Second table],
       ISC1.COLUMN_NAME,
       ISC1.DATA_TYPE        
  FROM INFORMATION_SCHEMA.COLUMNS ISC1
WHERE ISC1.COLUMN_NAME NOT IN (SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS ISC2 WHERE ISC2.TABLE_NAME = @T2)
        AND ISC1.TABLE_NAME = @T1
UNION ALL
— In TableTwo but not in TableOne
SELECT DISTINCT
       @T1 AS [First_table],
       ‘<<‘ AS Dir, –Direction
       @T2 AS [Second_table],
       ISC1.COLUMN_NAME,
       ISC1.DATA_TYPE        
  FROM INFORMATION_SCHEMA.COLUMNS ISC1
WHERE ISC1.COLUMN_NAME NOT IN (SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS ISC2 WHERE ISC2.TABLE_NAME = @T1)
        AND ISC1.TABLE_NAME = @T2
ORDER BY Dir DESC, COLUMN_NAME ASC

 

And the result is:
image

 

Good Luck

Share with:

FacebookTwitterGoogleLinkedIn


Leave a Comment