Use Tablediff to compare all tables
Solution 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
Solution 2
Try dbForge Scheme Compare for SQL server and dbForge Data Compare for SQL Server. It can compare and synchronize any database data and scheme. Quick, easy, always delivering a correct result.
Run it up on you database!
Comments
-
Davie over 1 year
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.
-
Davie almost 14 yearsCan u provide an example? Thanks!
-
Davie almost 14 yearsWhen I run this sql script there is an error where tablediff says it cant connect to the database. However, when I run the exact same query in a dos prompt it works without any problems i.e tablediff reports that the 2 tables are identical! Any ideas?
-
Irwin M. Fletcher almost 14 yearsI am not sure, this is a script that I have used many times, so I know that it works. If you add Print @sTableDiff to the script and then execute the output in the command prompt, does that work?
-
Christian Severin about 13 years+1 and a "bump!" for the linked article at weblogs.sqlteam.com/jeffs/archive/2004/11/10/2737.aspx and the elegant comparison query shown therein.