Compare stored procedures across multiple databases (SQL Server)

Question

SQL Gurus --

Our architecture consists of multiple customer databases to a common codebase. When we deploy database changes, the scripts must be run agianst each database.

Because of deployment issues, there have come times when our stored procedures became out of sync with one another. I would like to create a script to return these mimatched procedures to ensure that we have sync'd copies of our databases after deployment.

Is it possible to compare two or more databases, by having a script look at all the procedures between two databases, and return the mismatches?

Something to the effect of:

DATABASE_1 | DATABASE_2  | MISMATCHED_PROCEDURE | DATABASE_1_MODIFY_DATE | DATABASE_2_MODIFY_DATE
Customer_1 | Customer_2  | sp_get_names         | 1/1/2010               | 1/2/2010
Customer_1 | Customer_2  | sp_add_person        | 1/5/2010               | 1/6/2010

As a bonus, would it be possible to have the script automatically sync the databases by applying the newest script to the out-of-date script?

Much Thanks! George

Answer 1

There are many tools to do this. One of the best is Red-Gate SQL Compare. Another very good alternative is to use Visual Studio Database Professional to manage your database schema. Among other things, it will do very nice schema compares.

Answer 2

If you don't have SQL Compare or Visual Studio team system for DB architects (Data Dude)...play around with this...SQL 2005 and up

select t1.name,t1.modify_date,t2.modify_date
 from Database1.sys.procedures t1
join Database2.sys.procedures t2 on t1.name  = t2.name
and  object_definition(t1.object_id) <>  object_definition(t2.object_id)

Answer 3

Simplistic answer but a drop and create script on all procedures would be very easy and effective.

Answer 4

This is tangentially related, but I wrote something that provides percentage matching statistic between the text of two stored procedures: http://www.sqlservercentral.com/scripts/T-SQL/65787/

Answer 5

The Red Gate's Sql Compare is the perfect solution. However, if you can't afford its cost there is a very nice software that is free: Star Inix's Sql Compare http://www.starinix.com/sqlcompare02.htm