Using tablediff.exe

Some of the tools out for doing schema and data compare are really nice. SQLCompare is one that comes to mind. The problem - I don’t want to pay for them! Actually if I weren’t lazy, I would go download VS 2008 Team Edition for Database; I have access to it on MSDN and I believe that there is a diff tool in that version. But, I’ve been lazy lately. So for fun I went and checked out tablediff.exe. Tablediff.exe is shipped with SQL Server and was originally intended to be used by those running replication, so that they might compare replicated tables to source tables to see where differences may occur. (And, they definitely occur in SQL Server replication no doubt). So, I went and spent a few minutes checking the tool out.

First, it’s located somewhere in your SQL Server folders – mine was in c:\Program Files\Microsoft SQL Server\100\COM, probably in yours as well, but you may have multiple instances and a search will turn up a few of them scattered on your computer. It’s an .exe that doesn’t have a GUI of course, so you have to call it from the command line. Since I didn’t want to mess with all of that, I simply fired up xp_cmdshell in SQL Server:

sp_configure 'show advanced', 1
GO
RECONFIGURE
GO
sp_configure 'xp_cmdshell', 1
RECONFIGURE
GO

This will get you up and running, since all of that surface configuration garbage shuts off the good x procs nowadays. Next, in order to use this, I needed a table to compare, so I made one quickly:

SELECT * INTO Customers2 FROM customers
GO
DELETE Customers2 WHERE Region IS NULL
GO

Now I have a Customers and Customers2 table, with differences, so I can go use the tool and see what it comes up with. To get a simple row count, I used the following:

xp_cmdshell 'F:\Software\tablediff -q -sourceserver "BOOMERSOONER" -sourcedatabase "Northwind" –sourcetable 
"Customers" -destinationserver "BOOMERSOONER"  -destinationdatabase "Northwind" -destinationtable "Customers2"'

One caveat here – you can’t split the line of code up into two lines as I have here. xp_cmdshell doesn’t like that many characters, so keep it all in one line in the SSMS query window. Using the –q switch, I also get this:

output
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Microsoft (R) SQL Server Replication Diff Tool
Copyright (c) 2008 Microsoft Corporation
NULL
User-specified agent parameter values:
-q
-sourceserver BOOMERSOONER
-sourcedatabase Northwind
-sourcetable customers
-destinationserver BOOMERSOONER
-destinationdatabase northwind
-destinationtable customers2
NULL
Table [Northwind].[dbo].[customers] on BOOMERSOONER and Table [northwind].[dbo].[customers2] on BOOMERSOONER have different row counts.
Table [Northwind].[dbo].[customers] on BOOMERSOONER has 91 rows.
Table [northwind].[dbo].[customers2] on BOOMERSOONER has 31 rows.
The requested operation took 0.125004 seconds.
NULL

(17 row(s) affected)

Using the –f switch, I get this:

output
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Microsoft (R) SQL Server Replication Diff Tool
Copyright (c) 2008 Microsoft Corporation
NULL
User-specified agent parameter values:
-f
-sourceserver BOOMERSOONER
-sourcedatabase Northwind
-sourcetable customers
-destinationserver BOOMERSOONER
-destinationdatabase northwind
-destinationtable customers2
NULL
Table [Northwind].[dbo].[customers] on BOOMERSOONER and Table [northwind].[dbo].[customers2] on BOOMERSOONER have 60 differences.
Fix SQL written to DIFFIX.633780636432043095.sql.
Err    CustomerID    Col
Src. Only    N'ALFKI'   
Src. Only    N'ANATR'   
Src. Only    N'ANTON'   
Src. Only    N'AROUT'   
Src. Only    N'BERGS'   
…

There are some other switches as well, so go check them out. Overall, for a quick and free diff tool, it’s not too bad at all, really.

Lee

---------------------------------

Well, I guess you now know what college and team I am loyal to…