We recently need to find all the data which was different in 300 SQL Replicated tables on a pair of database. To find the differences, we were planning on using the TableDiff utility included with SQL 2005.
TableDiff Utility http://msdn.microsoft.com/en-us/library/ms162843.aspx
By default, the TableDiff creates a new *.SQL file for each table. There is not a way to concatenate output from multiple commands. Already you can see this wasn't going to be easy. We came up with a simple solution.
--Build TableDiff commands for each Article then execute these commands
select '"C:\Program Files\Microsoft SQL Server\90\COM\tablediff.exe" -sourceserver [SKOR380] -sourcedatabase [TRANPUB] -sourcetable [' + name +'] -sourceschema [dbo] -sourcelocked [TABLOCK] -destinationserver [SKOR380] -destinationdatabase [TRANSUB] -destinationtable [' + name + '] -destinationschema [dbo] -destinationlocked [TABLOCK] -f c:\temp\TableDiff' + cast(artid as varchar(100))+ '.sql' from sysarticles
Executing the SELECT statement we build a TEXT ouput of the unqiue TableDiff commands to execute in a CMD/DOS batch. DOS, wow, now I'm dating myself!
C:\Program Files\Microsoft SQL Server\90\COM\tablediff.exe -sourceserver [SKOR380] -sourcedatabase [TRANPUB] -sourcetable [Order Details] -sourceschema [dbo] -sourcelocked [TABLOCK] -destinationserver [SKOR380] -destinationdatabase [TRANSUB] -destinationtable [Order Details] -destinationschema [dbo] -destinationlocked [TABLOCK] -f c:\temp\1.sql
C:\Program Files\Microsoft SQL Server\90\COM\tablediff.exe -sourceserver [SKOR380] -sourcedatabase [TRANPUB] -sourcetable [Orders] -sourceschema [dbo] -sourcelocked [TABLOCK] -destinationserver [SKOR380] -destinationdatabase [TRANSUB] -destinationtable [Orders] -destinationschema [dbo] -destinationlocked [TABLOCK] -f c:\temp\2.sql
C:\Program Files\Microsoft SQL Server\90\COM\tablediff.exe -sourceserver [SKOR380] -sourcedatabase [TRANPUB] -sourcetable [Shippers] -sourceschema [dbo] -sourcelocked [TABLOCK] -destinationserver [SKOR380] -destinationdatabase [TRANSUB] -destinationtable [Shippers] -destinationschema [dbo] -destinationlocked [TABLOCK] -f c:\temp\3.sql
Executing these commands along with the COPY command builds on SQL file with all the necessary changes to bring the 2 servers in-sync.
--Concatenate all *.sql into one SQL file
Copy TableDiff*.sql TableDiffALL.sql
-- Database: [TRANSUB]
-- Table: [dbo].[Order Details]
UPDATE [dbo].[Order Details] SET [Quantity]=5 WHERE [OrderID] = 10250 AND [ProductID] = 51
-- Host: SKOR380
-- Database: [TRANSUB]
-- Table: [dbo].[Orders]
UPDATE [dbo].[Orders] SET [ShipName]='Pub1' WHERE [OrderID] = 10250
