Oscar Castillo

Editor's Note: Not only has this author produced a very useful stored procedure to compare SQL Server database structure and data, his code provides valuable insights in to how to write advancedTransact-SQL code. This article include much of the thought process used when the author created the sp_CompareDB stored procedure. Even if you don't understand all of the coding, you can still use thispowerful stored procedure in your day-to-day work. Instructions for creating and running the stored procedure are at the bottom of this article.


I bet almost every DBA has at times desperatelywanted to be able to compare data and structure between database tables, or even whole databases for that matter. Wouldn’t it be nice to have a stored procedure that uses only two parameters -- the nameof the first database and the name of the second database -- that compares structures of the databases and the data in them, and tells you the differences between them?

While it is relativelysimple to compare database or table structures, either programmatically or manually, comparing data is not so easy. Unlike comparing structures, comparing data between tables or databases is one that youcan’t easily do manually. I’m not saying that nobody likes this sort of intellectual work, comparing table-by-table, column-by-column in bulky queries using JOINs and NOT IN. I’m saying it’s usuallyphysically impossible to achieve if the databases are not diminutive, and mistakes are unavoidable.
What Does It Take to Write Such a Stored Procedure?
Well, let’s compare data structures and databetween 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 thedifferences. 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...