Compare SQL Server Databases with sp_CompareDB

What Does It Take to Write Such a Stored Procedure?

Well, let’s compare data structures and data between two databases programmatically. If we let ourselves think a bit about the implementation, we will come to a quick solution: create a cycle through all tables and compose SELECTs to obtain the differences. But when we start to put this primitive into practice, we run into a long series of “Oops!” Let me list just some of them I came upon while creating my SP to compare database structures and data.

1. Tables might or might not contain a unique key. It’s much easier to recognize differences in data and to expose them if there is a unique key for a table. First, I thought that since an absence of a unique key is a violation of 1NF, I could just skip such tables in the SP I was writing, and these tables could just be compared manually. However, after using the SP to compare various databases, I came to the conclusion that in the real world many databases do have tables without unique keys, so, it is better not to ignore them.
   
   For tables that have a unique key, my SP takes a look first for a primary key to compare and show differences. If there is no a primary key, then the first of unique constraint or unique index is used.
   
   
2. Before data can be compared, comparing the structures is a first step. Otherwise, you will compare apples and oranges. If you have this first step, then it is better to make it valuable and produce a clear report about the structural differences. So my SP does show structural differences like:
   
   authors
   Column phone: in db1 - char(12), in db2 - char(14)
   sales
   Column Location not in db2
   
   Tables with different structures are not used for comparing data. However, if the tables contain columns of the same type and different length (like "phone" in the example above), or if the tables have compatible data types (have the same type in syscolumns - char and nchar, varchar and nvarchar, etc.) they can have their data compared.
   
   
3. Datatypes text, ntext and image cannot be used in a predicate. In fact, it’s easily avoidable. You can use:
   
   SUBSTRING(<TextColumnName>,1,DATALENGTH(<TextColumnName>))
   
   in predicates for text and ntext data and just:
   
   DATALENGTH(<ImageColumnName>)
   
   for image data, supposing that if an image value was modified then its length was changed for sure.
   
   
4. Some columns in some tables store nulls. Even if you set ANSI_NULLS OFF, you cannot get TRUE when comparing two nulls. It’s possible if you compare two local variables or a database column value and a local variable, but comparing two columns with nulls will always return UNKNOWN. Anyway, you cannot use this SQL Server extension to the ANSI standard (SET ANSI_NULLS OFF) if you assume the comparison of databases when at least one of them resides on a linked server. In this case, SET ANSI_NULLS, as well as SET ANSI_WARNINGS, must be set to ON.
   
   
5. It’s apparently the case that comparing SELECTs must be executed dynamically, because they are generated on the fly. The problem is that some tables contain too many columns and those columns sometimes have very long names. I prefer self-explanatory names, but then I ran into an error message “Incorrect syntax near …” because the SELECT could not fit into the maximum of 8000 bytes, I was frustrated. There was no way to go ahead, but to limit the number of columns checked. The solution here involves a trick. I saved the strings containing predicates in a temporary table and then in a dynamic script I declared as many local variables with type varchar(8000) as necessary to cover all strings in the temporary table. Then I executed them as:
   
   EXEC (String1+String2+…)
   
   Thus, there is no restriction on the number of such strings and, therefore, there are no restrictions (except that SQL Server establishes) on the number of columns or name length in the tables of compared databases.
   
   
6. To compare two databases, at least one of them, by definition, must have 3-storey-qualified tables, because you cannot have two current databases. Therefore, the database name must be hard-coded in the script. To avoid this and make the script easy-to-run, we might use a so-called macro-substitution and make the script dynamic. (i.e., the SP is dynamic T-SQL code containing dynamic T-SQL code).
   
   
   
       Next Page>>