SQL with Manoj

Are you also looking for a Database diff tool that can provide you difference in 2 similar but different databases?

Few days back I was also searching for the same and stumbled over hell lot of tools. Found some good tools but they were trial-ware, limited to 14 or 30 days, but not worth to buy. Among them I liked SQL Delta, packed with lot of features.

SQL Delta provides you difference between 2 different database’s schemas and you can also compare data within those schemas. But the kind of html report it provides is not detailed and user friendly. I was looking for output on excel reports, thus thought to write my own logic to query the metadata of both the databases and get the difference, and here’s that:

 with cte as (
 select isnull(at.TABLE_CATALOG,'Database_A') as Database_A, at.TABLE_NAME as TBL_NME_A, ac.COLUMN_NAME as COL_NME_A,
 isnull(bt.TABLE_CATALOG,'Database_B') as Database_B, bt.TABLE_NAME as TBL_NME_B, bc.COLUMN_NAME as COL_NME_B
 from Database_A.INFORMATION_SCHEMA.TABLES at
 join Database_A.INFORMATION_SCHEMA.COLUMNS ac on ac.TABLE_NAME = at.TABLE_NAME
 left join Database_B.INFORMATION_SCHEMA.TABLES bt on bt.TABLE_NAME = at.TABLE_NAME
 left join Database_B.INFORMATION_SCHEMA.COLUMNS bc on bc.TABLE_NAME = bt.TABLE_NAME and bc.COLUMN_NAME = ac.COLUMN_NAME
 UNION
 select isnull(at.TABLE_CATALOG,'Database_A') as Database_A, at.TABLE_NAME as TBL_NME_A, ac.COLUMN_NAME as COL_NME_A,
 isnull(bt.TABLE_CATALOG,'Database_B') as Database_B, bt.TABLE_NAME as TBL_NME_B, bc.COLUMN_NAME as COL_NME_B
 from Database_B.INFORMATION_SCHEMA.TABLES bt
 join Database_B.INFORMATION_SCHEMA.COLUMNS bc on bc.TABLE_NAME = bt.TABLE_NAME
 left join Database_A.INFORMATION_SCHEMA.TABLES at on at.TABLE_NAME = bt.TABLE_NAME
 left join Database_A.INFORMATION_SCHEMA.COLUMNS ac on ac.TABLE_NAME = at.TABLE_NAME and ac.COLUMN_NAME = bc.COLUMN_NAME)
 select * from cte
 order by isnull(TBL_NME_A, TBL_NME_B), isnull(COL_NME_A, COL_NME_B)
 

The output of the above query gives you total 6 columns, 3 columns from first database & same 3 columns from second database. The columns listed are “Database Name”, “Table Name” & “Column Name”.

– If only “Column Name” values is absent then the tables exist but the column does not.

– If both “Table Name” & “Column Name” values are absent then the table does not exist.

The INFORMATION_SCHEMA.TABLES & INFORMATION_SCHEMA.COLUMNS views provides you information on underlying tables & columns of those tables. They contain metadata of the database, generally they internally use the sys views to get information, like sys.tables & sys.columns.

For more information on metadata & system catalog tables/views check my following post: https://sqlwithmanoj.wordpress.com/2010/12/06/querying-sql-server-metadata/