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!

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

You can identify which procedures (and other objects with slight modification) are different using the script below.

To synchronize databases you might want to try ApexSQL Diff. It’s similar to SQL Compare from Red Gate.

select S1.name [Db1_Schema], O1.name as [Db1_Object], O1.modify_date,
S2.name [Db1_Schema], O2.name as [Db1_Object], O2.modify_date
from database.sys.all_objects O1
inner join database2.sys.all_objects O2 on O1.name = O2.name
inner join database.sys.syscomments C1 on O1.object_id = C1.id
inner join database2.sys.syscomments C2 on O2.object_id = C2.id
inner join database.sys.schemas S1 on O1.schema_id = S1.schema_id
inner join database2.sys.schemas S2 on O2.schema_id = S2.schema_id
where C1.text <> C2.text and
-- remove the line below if you want to search all objects
O1.type = 'P'