Free SQL Server tablediff tool

Oopps! Upgrade your browser pretty please. Oopps! Upgrade your browser pretty please.

Sometimes you need to compare the schema or contents of two tables. There are many good tools for that available, but you might be a little surprised to find out that there is actually a free tool for that included with Microsoft SQL Server. It is command line based, and the file to run is called Tablediff.exe. It is located in the C:Program FilesMicrosoft SQL Server110COM folder (For SQL Server 2012 that is).

Basically it can compare 2 tables, both the schema and the data. And it can log errors to file or table, and even give you a SQL Server scriptfile that you can run on the destination database to make the changes needed for the 2 tables to be identical if any differences should be found.

A simple syntax example, that compares the sysjobshistory table in MSDB between SQLSERVER1 and SQLSERVER2 SQL Server instances. It does not specify any login information, so it will run with the Windows account of the logged on account execution it. And if differences are found, a script will be created in c:temptablediff.sql that will make the destination database identical to the source database: 

"C:Program FilesMicrosoft SQL Server110COMtablediff.exe" -sourceserver SQLSERVER1 -sourcedatabase msdb -sourceschema dbo -sourcetable sysjobhistory -destinationserver SQLSERVER2 -destinationdatabase msdb -destinationschema dbo -destinationtable sysjobhistory -f c:temptablediff.sql

It can for example be used to check for differences in replicated tables, but that only works if you replicate all columns or else the schemas of the two tables are so different that you get an error.

The full documentation of Tablediff.exe can be found here