SQL Server: how to use Tablediff.exe tool (Compare Two Datasources)

Need to compare two tables and determine what is missing from table #2?  Or, maybe you’ve got two tables on two different servers, and they need to be in synch?  The Tablediff utility was introduced with SQL v2005, and it can be used to reconcile data between two tables, or even views.  Tablediff.exe can be used to report variances in data AND schema.  Per BOL, it is used to compare the data in two tables for non-convergence, and is particularly useful for troubleshooting non-convergence in a replication topology.
It can be used to perform several different comparison type tasks.  You can look at the data row by row, or you can just compare the row counts.  You can perform just the schema comparison, or the data AND schema.  In my opinion, the best feature of tablediff is that you can generate the script to actually reconcile the differences between the datasources.
I’ve read that it was actually intended for replication, but it can be easily applied to any situation where you’re trying to compare data, schema, or both.  I can’t even begin to count the hours I have spent on ‘recon’ logic within the Chicago HFT (high frequency trading)  arena…  L.

Tablediff.exe can be found at \Program Files\Microsoft SQL Server\90\COM\TableDiff.exe, where SQL Server is installed.  This example will compare two tables in two different databases on two different servers, and it will generate the file that can ultimately be used to reconcile the differences:

"C:\Program Files\Microsoft SQL Server\90\COM\tablediff.exe" -sourceserver sourceservername                                                                                         -sourcedatabase sourcedatabase                                                                                        -sourcetable sourcetable                                                                                      
 -destinationserver destinationservername   -destinationdatabase destinationdatabase  -destinationtable destinationtable -et ChangeScript-f C:\Temp\ChangeScript.sql

tablediff.exe  is a Microsoft Internal tool in SQL Server 2008. this is used to check a quick comparison between 2 table in 2 different instanced.tablediff.exe can  do a comparison search on row level ,common level and schema level, this command is located in the C:\Program Files\Microsoft SQL Server\100\COM\ path,

syntax of tablediff.exe is as below

tablediff
[ -? ] |
{
        -sourceserver source_server_name[\instance_name]
        -sourcedatabase source_database
        -sourcetable source_table_name
    [ -sourceschema source_schema_name ]
    [ -sourcepassword source_password ]
    [ -sourceuser source_login ]
    [ -sourcelocked ]
        -destinationserver destination_server_name[\instance_name]
        -destinationdatabase subscription_database
        -destinationtable destination_table
    [ -destinationschema destination_schema_name ]
    [ -destinationpassword destination_password ]
    [ -destinationuser destination_login ]
    [ -destinationlocked ]
    [ -b large_object_bytes ]
    [ -bf number_of_statements ]
    [ -c ]
    [ -dt ]
    [ -et table_name ]
    [ -f [ file_name ] ]
    [ -o output_file_name ]
    [ -q ]
    [ -rc number_of_retries ]
    [ -ri retry_interval ]
    [ -strict ]
    [ -t connection_timeouts ]
}

implementation

“C:\Program Files\Microsoft SQL Server\100\COM\tablediff.exe” -sourceserver RAMESH-LT -sourcedatabase Replication_Test -sourcetable replic -destinationserver RAMESH-LT\MSSQL2K8 -destinationdatabase Replication_Test -destinationtable replic -et Difference

output screen

SQL Server 2005: TableDiff.exe GUI

Some time ago i've written about the TableDiff.exe utility. It's a great tool for quick table comparisons and i've grown tired of typing it in the command line.

That's why I created this simple TableDiff GUI to help me along and i've decided to share it with the world. :)




You can download it here.