How to run TableDiff utility for ALL replicated (published) tables in SQL 2005 or SQL 2008

We recently need to find all the difference in data between 2 SQL Servers each with 300 SQL replicated tables. To find the differences, we were planning on using the TableDiff utility included with SQL 2005 which is a command line tool designed to provide TSQL needed to manually sync two table in two different databases.

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

By default, you execute TableDiff for each table and output individual *.SQL script files.  There is not an automatic way to execute TableDiff for all table or to concatenate output from multiple commands into one *.SQL script file.  Already you can see this wasn’t going to be easy.  However, we came up with a simple solution.

1. Execute a SELECT statement whose output if a string concatenation to build Tabldiff command
2. Paste all the TableDiff output from command above into 1 *.BAT or *.CMD file
3. Execute the *.BAT/CMD file which calls TableDiff for each table.
4. Concatenate the TSQL statements generated by TableDiff into one *.SQL file
5. Execute the single *.SQL file to bring the tables in-sync.

Build TableDiff Commands for all tables listed in SYSARTICLES

--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

Generic method to Build TableDiff Commands for ALL Tables.

--Below is a generic version to create TableDiff for all user defined tables
--SQL 2008 set path to ...100\COM\tablediff.exe

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 SQL Statements

--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

Execute TableDifffALL.sql