Use Tablediff to compare all tables

Question

I have recently discovered the tablediff utility of SQL Server 2005.

I have 2 instances of the same database each on a different server.

Is it possible to compare all tables using tablediff without having to replicate the same command while only changing the table name?

For example, compare table1 on server1 with table1 on server2 then compare table2 on server1 with table2 on server2, until all tables have been compared.

Answer 1

This can be done by setting the -sourceserver option of the tablediff utility to the first server and the -destinationserver option to the second server. You can use the sys.Tables to iterate through each table in the database so that you can automate this process.

Edited

I also wanted to point out this article which is a clever piece of t-sql code that may serve you better without the complications of tablediff

Per your comment, here is an example. This is not an optimal way to do this in a production enviorment, but it should get the job done for you. You would probably be better off implementing this in SSIS if you need a more production worthy option.

SET QUOTED_IDENTIFIER ON 

DECLARE @TableNames as table (
    id int identity(1,1),
    tableName varchar(100))

DECLARE @sTableDiff nvarchar(1000)
DECLARE @tableName varchar(100)
DECLARE @counter int
DECLARE @maxCount int

INSERT INTo @TableNames 
SELECT name 
FROM sysobjects WHERE type = 'U'

SET @counter = 1

SELECT @maxCount = COUNT(name) 
FROM sysobjects WHERE type = 'U'

WHILE @counter < @maxCount
    Begin
        SELECT @tableName = tableName 
        FROM @TableNames 
        WHERE id = @counter

        SET @sTableDiff= ' "C:\Program Files\Microsoft SQL Server\90\COM\tablediff" -sourceserver Server1 
            -sourceuser sa -sourcepassword password -sourcedatabase YourDatabase -sourcetable ' + @tableName + ' 
            -destinationserver Server2 -destinationuser sa -destinationpassword password -destinationdatabase 
            YourDatabase -destinationtable ' + @tableName + '  -f c:\Diff'      

        EXEC XP_CMDSHELL @sTableDiff

        Set @counter = @counter + 1
    End