SQL Server 2008 Command-Line Utilities : The tablediff Command-Line Utility

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
[ -? ] |
{
        -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 ]
}


					    

The tablediff syntax requires source and destination connection information to perform a comparison. This information includes the servers, databases, and tables that will be compared. Connection information must 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 database. The simplest way to create some data for comparison is to select the contents of one table into another and then update some of the rows in one of the tables. The following SELECT statement makes a copy of the AddressType table in the AdventureWorks2008R2 database to the AddressTypeCopy table:

select *
 into Person.AddressTypeCopy
 from Person.AddressType

In addition, the following 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 New'
 WHERE AddressTypeId = 1

UPDATE Person.AddressTypeCopy
 SET Name = 'Shipping New',
  ModifiedDate = '20090918'
 WHERE AddressTypeId = 5

The tablediff utility can be executed with the following 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 following output to the command prompt window:

User-specified agent parameter values:
-sourceserver (local)
-sourcedatabase AdventureWorks2008R2
-sourceschema Person
-sourcetable AddressType
-destinationserver (local)
-destinationdatabase AdventureWorks2008R2
-destinationschema Person
-destinationtable AddressTypeCopy
-f c:\TableDiff_Output

Table [AdventureWorks2008R2].[Person].[AddressType] on (local)
and Table [AdventureWorks2008R2].[Person].[AddressTypeCopy] on (local)
have 2 differences.
Fix SQL written to c:\TableDiff_Output.sql.
Err     AddressTypeID   Col
Mismatch        1       Name
Mismatch        5       ModifiedDate Name
The requested operation took 0.296875 seconds.

The output first displays a summary of the parameters used and then shows the comparison results. In this example, it found the two differences that are due to updates performed on AddressTypeCopy. In addition, the –f parameter used in the example caused the tablediff utility to output a SQL file that can be used to fix the differences in the destination table. The output file from this example looks as follows:

— Host: (local)
— Database: [AdventureWorks2008R2]
— Table: [Person].[AddressTypeCopy]
SET IDENTITY_INSERT [Person].[AddressTypeCopy] ON
UPDATE [Person].[AddressTypeCopy]
 SET [Name]='Billing'
 WHERE [AddressTypeID] = 1
UPDATE [Person].[AddressTypeCopy]
 SET [ModifiedDate]='2002-06-01 00:00:00.000',
 [Name]='Shipping' WHERE [AddressTypeID] = 5
SET IDENTITY_INSERT [Person].[AddressTypeCopy] OFF

Note

The tablediff utility requires the source table to have at least one primary key, identity, or ROWGUID column. This gives the utility a key that it can use to try to match a corresponding row in the destination table. If the –strict option is used, the destination table must also have a primary key, identity, or ROWGUID column.

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 –strict 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 (for example, INT can be compared to BIGINT).

The tablediff 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.