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


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


Comments (1)

  1. Tabbs says:

    thank you, very much.

    this script came in handy when i wanted to update or rather synchronize two databases on a peer to peer transactional replication.

    and the best part is that i did it remotely without having to go to the clients site.

    kudos.

Skip to main content