Compare SQL Server Databases with sp_CompareDB
H. The behavior of the SQL variant data type is quite specific when you compare data of different data type families. However, when using my SP, we don’t suppose to compare different data types to determine what is bigger (and get eyebrow-raising results when 1 is bigger then ’9999′), we need only two-
state replies: “equal/not equal”. Our concern is:
1. not to get “equal” when data are different in fact
2. not to get “different” when data are equal in fact
The first mistake would be possible if a SQL_variant is converted during the comparison. However, the SQL_variant data type families are always strictly ranked in comparison operations, and as a result, for example, binary 0×61 will never equal char(1) ‘A’. Excellent!
CREATE TABLE ChkSQLv (CharSQLvCol SQL_variant, BinarySQLvCol SQL_variant)
INSERT INTO ChkSQLv VALUES (CONVERT(char(1),’A’),CONVERT(Binary(1),0×61))
SELECT * FROM ChkSQLv WHERE ISNULL(CharSQLvCol,’!#null$’)=ISNULL(BinarySQLvCol,’!#null$’)
will return nothing as well as:
SELECT * FROM ChkSQLv WHERE BinarySQLvCol=CharSQLvCol
CREATE TABLE ChkBinChar (CharCol char(1), BinaryCol binary(1))
INSERT INTO ChkBinChar VALUES (‘A’,0×61)
SELECT * FROM ChkBinChar WHERE BinaryCol=CharCol
returns the row!
A SQL_Variant null does not have a basic data type, therefore being a subject of ISNULL, it never produces “Converting error”:
DECLARE @SQLvCol SQL_Variant
SET @SQLvCol = CONVERT(int,null)
While this code produces the “Converting error” message:
DECLARE @IntCol int
SET @IntCol = null
How to Run sp_CompareDB Stored Procedure
To make a long story short, it was quite a challenge not just to create a script comparing databases, but also to make it free of excessive restrictions, fast, and easy-to-run. All you need to install the sp_CompareDB stored procedure is to execute the script against the Master database. Then set the Query Analyzer option “Results in Text”) to get the result sets and comments on the same page).
Once the stored procedure has been created, simply run:
And enjoy seeing the differences.
If you are comparing a database from a linked server, then the command would be:
You can use the following parameters for the SP, besides the obvious db1 name (parameter name @db1) and db2 name (parameter name @db2).
@TabList – comma separated list of tables to compare. If empty – all tables in the databases should be compared
@NumbToShow – number of rows with differences to show. Default – 10. Be cautious increasing it. For tables with a unique key just the keys of rows with differences should be shown. But for tables without a unique key, all data for rows with differences are shown. After all it’s unlikely that somebody needs a boundless report to get lost in the ocean of digits and letters. If databases are completely different then there is no sense comparing them.
@OnlyStructure – flag being set to 1 allows you to avoid data comparing. Only structures should be compared. Default 0.
@NoTimestamp – flag being set to 1 allows you to avoid comparing of columns with timestamp data type. Default 0.
@VerboseLevel – flag being set to 1 lets you see queries used for data comparison. They can be used easily to synchronize data in the databases if you need it. Default 0.
A final note: This SP is absolutely safe. It only gets data from databases while not making any changes. The databases can even be in read-only mode. However, if you have big tables without a unique key, leave enough free disk space before running the SP. The space available must be at least twice bigger then the space consumed by the largest table in the databases.
Published with the explicit written permission of the author. Copyright 2001.