How can I compare that the contents of two tables are identical?

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.