Implement symmetric difference in SQL Server?

Question

Here's a problem I've been trying to solve at work. I'm not a database expert, so that perhaps this is a bit sophomoric. All apologies.

I have a given database D, which has been duplicated on another machine (in a perhaps dubious manner), resulting in database D'. It is my task to check that database D and D' are in fact exactly identical.

The problem, of course, is what to actually do if they are not. For this purpose, my thought was to run a symmetric difference on each corresponding table and see the differences.

There is a "large" number of tables, so I do not wish to run each symmetric difference by hand. How do I then implement a symmetric difference "function" (or stored procedure, or whatever you'd like) that can run on arbitrary tables without having to explicitly enumerate the columns?

This is running on Windows, and your hedge fund will explode if you don't follow through. Good luck.

Answer 1

My first reaction is to suggest duplicating to the other machine again in a non-dubious manner.

If that is not an option, perhaps some of the tools available from Red Gate could do what you need.

(I am in no way affliated with Red Gate, just remember Joel mentioning how good their tools were on the podcast.)

Answer 2

Use the SQL Compare tools by Red Gate. It compares scheamas, and the SQL Data Compare tool compares data. I think that you can get a free trial for them, but you might as well buy them if this is a recurring problem. There may be open source or free tools like this, but you might as well just get this one.

Answer 3

Here is the solution. The example data is from the ReportServer database that comes with SSRS 2008 R2, but you can use it on any dataset:

SELECT s.name, s.type 
FROM 
(
    SELECT s1.name, s1.type
    FROM syscolumns s1
    WHERE object_name(s1.id) = 'executionlog2'
    UNION ALL 
    SELECT s2.name, s2.type
    FROM syscolumns s2 
    WHERE object_name(s2.id) = 'executionlog3'
) AS s 
GROUP BY s.name, s.type   
HAVING COUNT(s.name) = 1