SQL Server 2008 R2 tablediff CLI Tool
The tablediff utility enables you to compare the contents of two tables. It was originally developed for replication scenarios to help troubleshoot nonconvergence, but it is also very useful in other scenarios. When data in two tables should be the same or similar, this tool can help determine whether they are the same, and if they are different, it can identify what data in the tables is different. The syntax for tablediff is as follows:
tablediff
[ -? 1 I
{
-sourceserver source_server_name[\instance_name]
-sourcedatabase source_DB
-sourcetable source_table_name
[ -sourceschema source_schema_name ]
[ -sourcepassword source_password ]
[ -sourceuser source_login ]
[ -sourcelocked ]
-destinationserver destination_server_name[\instance_name]
-destinationdatabase subscription_DB
-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 1
[ -rc number_of_retries ]
[ -ri retry_interval ]
[ -strict ]
[ -t connection_timeouts ]
}
The tablediff syntax requires source and destination connection info to execute a comparison. This info includes the servers, DBs, and tables that will be compared. Connection info need to be provided for SQL Server authentication but can be left out if Windows authentication can be used. The source and destination parameters can be for two different servers or the same server, and the tablediff utility can be run on a machine that is neither the source nor the destination. To illustrate the usefulness of this tool, let's look at a sample comparison in the AdventureWorks2008R2 DB. The simplest way to create some data for comparison is to choose the contents of one table into another and then update some of the rows in one of the tables. The subsequent CHOOSE statement makes a copy of the AddressType table in the AdventureWorks2008R2 DB to the AddressTypeCopy table:
choose *
into Person.AddressTypeCopy
from Person.AddressType
In addition, the subsequent statement updates two rows in the AddressTypeCopy table so that you can use the tablediff utility to identify the changes:
UPDATE Person.AddressTypeCopy
SET Name = 'Billing New1
WHERE AddressTypeld = 1
UPDATE Person.AddressTypeCopy
SET Name = 'Shipping New',
ModifiedDate = '2009O918'
WHERE AddressTypeld = 5
The tablediff utility can be executed with the subsequent parameters to identify the differences in the AddressType and AddressTypeCopy tables:
tablediff -sourceserver "(local)" -sourcedatabase "AdventureWorks2008R2"
-sourceschema "Person"-sourcetable "AddressType"
-destinationserver "(local)" -destinationdatabase "AdventureWorks2008R2"
-destinationschema "Person" -destinationtable "AddressTypeCopy"
-f c:\TableDiff_Output.txt
The destination and source parameters are the same as in the previous example, except for the table parameters, which have the source AddressType and the destination AddressTypeCopy. The execution of the utility with these parameters results in the subsequent output to the command prompt window:
User-specified agent parameter values:
-sourceserver (local)
-sourcedatabase AdventureWorks2O08R2
-sourceschema Person
-sourcetable AddressType
-destinationserver (local)
-destinationdatabase AdventureWorks2O08R2
-destinationschema Person
-destinationtable AddressTypeCopy
-f c:\TableDiff_Output
Table [AdventureWorks2O08R2].[Person].[AddressType] on (local)
and Table [AdventureWorks2O08R2].[Person].[AddressTypeCopy] on (local)
have 2 differences.
Fix SQL written to c:\TableDiff_Output.sql.
Err AddressTypelD Col
Mismatch 1 Name
Mismatch 5 ModifiedDate Name
The requested operation took O.296875 seconds.
The output first displays a summar y of the parameter s used and then displays the comparison results. In thi s example, it found the two differences tha t are due to updates performed on AddressTypeCopy. In addition, the -f parameter used in the exampl e caused the tablediff utility to output a SQL file tha t can be used to fix the differences in the destination table. The output file from thi s example looks as follows:
— Host: (local)
— DB: [AdventureWorks2O08R2]
— Table: [Person].[AddressTypeCopy]
SET IDENTITY_INSERT [Person].[AddressTypeCopy] ON
UPDATE [Person].[AddressTypeCopy]
SET [Name]='Billingl
WHERE [AddressTypelD] = 1
UPDATE [Person].[AddressTypeCopy]
SET [ModifiedDate]='2002-06-01 00:00:00.000',
[Name]='Shipping' WHERE [AddressTypelD] = 5
SET IDENTITY_INSERT [Person].[AddressTypeCopy] OFF
Keep in mind that several different types of comparisons can be done with the tablediff utility. The -q option causes a quick comparison that compares only record counts and looks for differences in the schema. The -stric t option forces the schemas of each table to be the same when the comparison is run. If this option is not used, the utility allows some columns to be of different data types, as long as they meet the mapping requirements for the data type (as an example, INT can be compared to BIGINT). The tabledif f utility can be used for many different types of comparisons. How you use this tool depends on several factors, including the amount and type of data you are comparing.