TableDiff.exe Utility

Tablediff.exe  command line utility

SQL server has command line utility tablediff.exe, using this we can easily compare source and destination tables. This is generally used in replication topology.

We can use it from command prompt or by using batch file.

We can perform following

1.       Row by row comparison

2.       Row count or schema comparison

3.       Column level comparison

4.       Generate t-SQL script to have data in sync with source and destination

5.       Log comparison output into file or in destination database table.

Utility required list of parameters but some rudimentary parameters are below

1.       -sourceserver 

2.       -sourcedatabase 

3.       -sourcetable

4.       -destinationserver 

5.       -destinationdatabase 

6.       -destinationtable 

Let us create tables for source and destination comparison

Source Table:

CREATE TABLE TableDiffSource

(TDID INT PRIMARY KEY, TDDescription VARCHAR(100))

INSERT INTO TableDiffSource

SELECT 1,'A'

UNION

SELECT 2,'B'

UNION

Select 4,'D'

Destination Table:

CREATE TABLE TableDiffDestination

(TDID INT PRIMARY KEY, TDDescription VARCHAR(100))

INSERT INTO TableDiffDestination

SELECT 1,'A'

UNION

SELECT 2,'B'

UNION

SELECT 3,'C'

Open the command prompt and execute below command by changing your Server & Database names.

In command I have used. 

SourceServcer: SQLSourceServerName & SourceDatabase= Capital

DestinationServer= SQLDestinationServerName & DestinationDatabase=  Capital

C:\Program Files\Microsoft SQL Server\120\COM>tablediff.exe -sourceserver SQLSourceServerName -sourcedatabase Capital -sourcetable tablediffsource -destinationserver

 SQLDestinationServerName -destinationdatabase Capital -destinationtable tablediffdestinat

ion -et tableDiffoutput

It will look like below after execution of tablediff.exe utility, in image i have hidden soruceserver & destinationServer names, please change them as per your SQL Server details

Let us check the difference, in output table

select * FROM tablediffoutput

output

TDID	MSdifftool_ErrorCode	MSdifftool_ErrorDescription	my Comments
3	1	Dest. Only	Present in Destination but not in source table
4	2	Src. Only	Present in source but not in destination table

Note: in above output, column myComments I have added for more clarification marked in color olive green.

for more details, please visit below link .

https://msdn.microsoft.com/en-IN/library/ms162843.aspx