How to compare data between two table in different databases using Sql Server 2008?

Question

I have two database's, named DB1 and DB2 in Sql server 2008. These two database's have the same tables and same table data also. However, I want to check if there are any differences between the data in these tables.

Could anyone help me with a script for this?

Answer 1

select *  from (       select 'T1' T, *       from DB1.dbo.Table       except       select 'T2' T, *       from DB2.dbo.Table      ) as T union all select *  from (       select 'T2' T, *       from DB2.dbo.Table       except       select 'T1' T, *       from DB1.dbo.Table      ) as T ORDER BY 2,3,4, ..., 1  -- make T1 and T2 to be close in output 2,3,4 are UNIQUE KEY SEGMENTS 

Test code:

declare @T1 table (ID int) declare @T2 table (ID int)  insert into @T1 values(1),(2) insert into @T2 values(2),(3)  select *  from (       select *       from @T1       except       select *       from @T2      ) as T union all select *  from (       select *       from @T2       except       select *       from @T1      ) as T 

Result:

ID ----------- 1 3 

Note: It can take long time to compare big table, when developing "tuned" solution or refactorig, which will give same result as REFERERCE - it may be wise to chekc simple parameters first: like

select count(t.*) from (    select count(*) c0, SUM(BINARY_CHECKSUM(*)%1000000) c1 FROM T_REF_TABLE     -- select 12345 c0, -214365454 c1 -- constant values FROM T_REF_TABLE     except     select count(*) , SUM(BINARY_CHECKSUM(*)%1000000) FROM T_WORK_COPY  ) t 

When this is empty, you have probably things under controll, and may be you can modify when you fail you will see "constant values FROM T_REF" to isert to save even more time for next check!!!