Compare Databases' Structural Differences

I created a stored procedure, sp_DBDiff, that lets you compare the structural differences between two databases. The generated report lists new tables, dropped tables, new columns, dropped columns, and column definition changes.

To compare the differences between two databases, you use an EXECUTE statement that follows the syntax

EXEC sp_DBDiff @OldDB =  'MyDatabaseVersion1', @NewDB =  'MyDatabaseVersion2' 

where MyDatabaseVersion1 and MyDatabase-Version2 are the two databases you want to compare. (Although this query appears on several lines here, you would enter it on one line in the command-shell window. The same holds true for the other multiline commands in this article.)

Listing 2 shows an excerpt from sp_DBDiff.As callout A and callout B show, the stored procedure requires that another stored procedure, sp_Cols, be installed in the master database.The sp_Cols stored procedure obtains the column definitions for a specified table, along with an estimate of the minimum and maximum number of records that would fit on a data page.

You can use the sp_Cols stored procedure by itself. If you specify wildcards for the table name and column name, you can perform some creative searches. For example, if you want to see all the columns that start with Province in a database, you'd use the EXECUTE statement

EXEC sp_Cols @column =  'Province%' 

If you want to see all the columns for all the tables that start with Admin, you'd run the command

Exec sp_Cols @table = 'Admin%' 

I wrote the sp_DBDiff and sp_Cols stored procedures for use on SQL Server 2000.You can download fully commented versions of these stored procedures from the SQL Server Magazine Web site.
— Bill McEvoy