A. A couple of methods :-
1. BCP them both out and use the NT COMP.EXE command to compare them.
2. (Courtesy of Roy Harvey)
If tableA and tableB have unique indexes on them:
select count(*) from tableA
select count(*) from tableB
If they don't have unique indexes on them, then do
select count(*) from (select distinct * from tableA) as a
select count(*) from (select distinct * from tableB) as b
Check that the counts are the same. If they are not then obviously the tables don't match. If the counts do match then do:
select count(*)
from (select * from tableA
UNION
select * from tableB) as t
If the count from this query is the same as the previous counts, then the two tables are identical. This is because a UNION does a DISTINCT merge of the two resultsets. Therefore the two tables will "collapse" back into one if and only if they are identical.
