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

Share this Post

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

Share this Post
Tags:

5 thoughts on “How to run TableDiff utility for ALL replicated (published) tables in SQL 2005 or SQL 2008”

  1. Hi,

    I recently ran into a problem using tablediff in SQL Server 2008 when using the -f option against tables with decimal data types containing nulls.  Do you know of another way around this other than using the SQL Server 2005 utility?  I did not get an error when I used the 2005 tablediff.  Thanks.

  2. I discussed this with MS under a problem ticket and the whole null issue is suppsed to be resolved in SP2. Having said that; i've seen posts about this NULL issue since sqlserver 2000…who knows.

  3. Hi,

    I am getting the following error while i am using tablediff

    "unable to create UPDATE for record with only large data types"

    i dnt have large data types column in table.

    any suggestion

  4. If you are truly comparing one table on one server to the same table on a different server, must you have a linked server set up across?  I created some tables to compare locally and tablediff was able to tell the difference, but when trying to comparing other tables that are on separate servers, it claims they are identical.  Am I missing something?

  5. What a horrid way to compare what should be a simple “here compare this with this!”.
    Forget this guys. Download TOAD for SQL Server and save your scalp!

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.