I have been a long time fan of RedGate's SQL Compare tool to compare all aspects of a database. The work I'm doing at the moment needs me to integrate some new code into an existing project - some of which involves database manipulation. However, I'm only interested in a subset of stored procedures as many of them are different for valid reasons. I couldn't work out how to do this in SQL Compare so, I wrote a script to do it for me. It is called spu_CompareProcedures and I thought I'd share it in the hope that is of value to others.

There are 5 parameters available, they are:

  • db1 - the name of the 1st database to use
  • db2 - the name of the 2nd database to use
  • proceduresToCompare - either set to '' to compare ALL stored procedures in both databases, or set to a comma separated list to restrict what gets checked
  • displayOnlyDifferent - defaults to 1, i.e. only display the stored procedures which are different. If set to 0 it'll show the ones that are the same as well, but in most cases this is just too much information
  • debug - defaults to 0. Used to output the SQL being generated behind the scenes to enable debugging of this stored procedure

If I wanted to compare the procedures spu_generateinsert and spu_compareprocedures on the database MyMasterDatabase and MyOtherDatabase I would specify
spu_compareprocedures @db1 = 'MyMasterDatabase', @db2 = 'MyOtherDatabase', @proceduresToCompare='spu_generateinsert,spu_compareprocedures', @displayOnlyDifferent=1, @debug=0

As with spu_generateinsert, the output is displayed in the Messages window. It will report on what is being checked, any errors - i.e. procedures which exist in one database and not the other, or maybe either, and those which are different (or the same as well depending on the value of @displayOnlyDifferent). Sample output is:
******************************************************************
Comparing databases MyMasterDatabase and MyOtherDatabase
Objects:
spu_generateinsert
spu_compareprocedures
******************************************************************

*****************
** ERRORS **
*****************

spu_compareprocedures is missing for MyMasterDatabase
spu_compareprocedures is missing for MyOtherDatabase

*****************
** COMPARISONS **
*****************

No differences found

It is very simple, and just checks the contents of the stored procedure, it doesn't attempt to identify the differences. But, at least theoretically, this is enough to give a heads up as to the procedures to be concerned about.

Download the script here.

If you have suggestions, comments or bug reports, please leave me a comment and I'll do what I can to help out.