We recently need to find all the data which was different in 300 SQL Replicated tables on a pair of database.  To find the differences, we were planning on using the TableDiff utility included with SQL 2005. 

TableDiff Utility http://msdn.microsoft.com/en-us/library/ms162843.aspx

By default, the TableDiff creates a new *.SQL file for each table.  There is not a way to concatenate output from multiple commands.  Already you can see this wasn't going to be easy.  We came up with a simple solution.

--Build TableDiff commands for each Article then execute these commands

--SQL 2005 format

select '"C:\Program Files\Microsoft SQL Server\90\COM\tablediff.exe" -sourceserver [SKOR380] -sourcedatabase [TRANPUB] -sourcetable [' + name +'] -sourceschema [dbo] -sourcelocked [TABLOCK] -destinationserver [SKOR380] -destinationdatabase [TRANSUB] -destinationtable [' + name + '] -destinationschema [dbo] -destinationlocked [TABLOCK] -f c:\temp\TableDiff' + cast(artid as varchar(100))+ '.sql' from sysarticles

 

--SQL 2008 set path to ...100\COM\tablediff.exe

select '"C:\Program Files\Microsoft SQL Server\100\COM\tablediff.exe" -sourceserver [SKOR380] -sourcedatabase [TRANPUB] -sourcetable [' + name +'] -sourceschema [dbo] -sourcelocked [TABLOCK] -destinationserver [SKOR380] -destinationdatabase [TRANSUB] -destinationtable [' + name + '] -destinationschema [dbo] -destinationlocked [TABLOCK] -f c:\temp\TableDiff' + cast(artid as varchar(100))+ '.sql' from sysarticles

 

 

--Below is a generic version to create TableDiff for all user defined tables

Declare @sourceserver nvarchar(100) = 'sourcesqlservername\instancename'

Declare @destinationserver nvarchar(100) = 'destinationsqlservername\instancename'

 

select '"C:\Program Files\Microsoft SQL Server\90\COM\tablediff.exe" -sourceserver [' + @sourceserver +

'] -sourcedatabase [AdventureWorksLT_TranSub1] -sourcetable [' + name +'] -sourceschema [' + SCHEMA_NAME(schema_id ) +

'] -sourcelocked [TABLOCK] -destinationserver [' + @destinationserver + '] -destinationdatabase [AdventureWorksLT_TranSub2] -destinationtable [' + name +

'] -destinationschema ['+ SCHEMA_NAME(schema_id )  +'] -destinationlocked [TABLOCK] -f c:\temp\TableDiff_' + name+ '.sql'

      from  sys.tables 

      where type_desc = 'USER_TABLE'

Executing the SELECT statement we build a TEXT ouput of the unqiue TableDiff commands to execute in a CMD/DOS batch.  DOS, wow, now I'm dating myself!

Sample automatic TableDiff commands
C:\Program Files\Microsoft SQL Server\90\COM\tablediff.exe -sourceserver [SKOR380] -sourcedatabase [TRANPUB] -sourcetable [Order Details] -sourceschema [dbo] -sourcelocked [TABLOCK] -destinationserver [SKOR380] -destinationdatabase [TRANSUB] -destinationtable [Order Details] -destinationschema [dbo] -destinationlocked [TABLOCK] -f c:\temp\1.sql
C:\Program Files\Microsoft SQL Server\90\COM\tablediff.exe -sourceserver [SKOR380] -sourcedatabase [TRANPUB] -sourcetable [Orders] -sourceschema [dbo] -sourcelocked [TABLOCK] -destinationserver [SKOR380] -destinationdatabase [TRANSUB] -destinationtable [Orders] -destinationschema [dbo] -destinationlocked [TABLOCK] -f c:\temp\2.sql
C:\Program Files\Microsoft SQL Server\90\COM\tablediff.exe -sourceserver [SKOR380] -sourcedatabase [TRANPUB] -sourcetable [Shippers] -sourceschema [dbo] -sourcelocked [TABLOCK] -destinationserver [SKOR380] -destinationdatabase [TRANSUB] -destinationtable [Shippers] -destinationschema [dbo] -destinationlocked [TABLOCK] -f c:\temp\3.sql

Executing these commands along with the COPY command builds on SQL file with all the necessary changes to bring the 2 servers in-sync.

--Concatenate all *.sql into one SQL file
Copy TableDiff*.sql TableDiffALL.sql

Sample Output

-- Database: [TRANSUB]
-- Table: [dbo].[Order Details]
UPDATE [dbo].[Order Details] SET [Quantity]=5 WHERE [OrderID] = 10250 AND [ProductID] = 51
-- Host: SKOR380
-- Database: [TRANSUB]
-- Table: [dbo].[Orders]
UPDATE [dbo].[Orders] SET [ShipName]='Pub1' WHERE [OrderID] = 10250