Tuesday, January 4, 2011

tablediff command line utility in SQL Server

Above tablediff utility is very useful for comparing tables.It is not only tells us which records / columns are different but can also generate a SQL script to update the second table to make it the same as the first table.

You have to navigate to following path the use this utility

SELECT 'C:\Program Files\Microsoft SQL Server\100\COM>

Then type command ;

tablediff –?

Then it shows all the options as follows

But only limitation is you could compare one table at a time. Even though you can do some interesting work to overcome this. That is Just go to the SQL sever and type following command execute it. So it will generate the script for all the table. Then you can add it into .bat file.

SELECT 'C:\Program Files\Microsoft SQL Server\100\COM>tablediff -sourceserver "SourceDBName" -sourcedatabase "T1" -sourcetable ' + '"' + name +'" ' + '-destinationserver "DestinationDBName" -destinationdatabase "T2" -destinationtable ' + '"' + name +'" ' + ' -o d:\' + name +'_Script_out.txt'
FROM dbo.sysobjects
WHERE xtype = 'U'