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!

Griffon

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.

Cuirassier

SQL Compare is exactly what came to my mind, great suggestion! red-gate.com/products/SQL_Compare/index.htm – Bellda

Excellent tool - and not just this one - everything from Redgate is top-notch. – Beehive

@Beehive - Agreed. Almost anything from Red-Gate is gold. – Cuirassier

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' 

Senarmontite

thanks...... it works.....but when i run in two environments i'm getting more records. 107 procedures were available in each db, but when the above query executed i am getting more than 1,00,000 records..plz suggest – Ledeen

This code tells all my SPs are different (which they're not) i.e. the comparison C1.text <> C2.text is always true. I tried object_definition(t1.object_id) <> object_definition(t2.object_id) from the answer below and that sorted it – Ocrea

Answer 3

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)

Island

It looks nice but I have definition only for current db despite of using db or alias before object_id. From Microsoft: "The SQL Server Database Engine assumes that object_id is in the current database context. The collation of the object definition always matches that of the calling database context." – Latchkey

Answer 4

Use the following:

   SELECT DISTINCT
      o1.name AS Object_Name1,
      o1.type_desc as type_desc1,
      o2.name AS Object_Name2,
      o2.type_desc as type_desc2
   FROM DB1.sys.sql_modules m1
   INNER JOIN DB1.sys.objects o1
   ON m1.object_id = o1.object_id
   FULL OUTER JOIN DB2.sys.sql_modules m2
   INNER JOIN DB2.sys.objects o2
   ON m2.object_id = o2.object_id
   ON o1.name = o2.name
   WHERE isnull(m2.definition,'') <> isnull(m1.definition,'')        

Jeane

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

Comeuppance

not polished but really a nice little tool...considering it's free. It does a pretty good job for basic comparisons of dbs but have to watch out for the cnet spam-ware stuff on install. – Zito

The URL as listed did not work for me. However, the URL starinix.com/sqlcompare02.htm did work. – Communard

Answer 6

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

Plummy

Answer 7

Yes, the RedGate stuff is great, but that is what I have done to compare the stored procedures in two different databases:

1. Scripted all stored procedure into separate files. You can do that through the Microsoft SQL Server Management Studio wizard.
2. Did the same from the other database that I am compare against.
3. Started KDiff3 which is free I believe.
4. Gave it the two directories that it needs to troll through.
5. Now inspect your results by double-clicking where you see red and panel below will tell you the differences.

Done!

Bruns

Answer 8

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/

Nebulose

Answer 9

If you want to compare all the stored procedures from both databases and output names of those that do not exist in the other, then use the following. Note this does not check the definition of the stored procedures only its name, but there is a way to check for that too.

-- Declare 2 variable names to hold the name of the databases
DECLARE @DB1 varchar(50)
SET @DB1 = 'nameOfDb1'
DECLARE @DB2 varchar(50)
SET @DB2 = 'nameOfDb2'
EXEC('SELECT
        t1.name,
        t2.name
      FROM '
        + @DB1 +'.sys.procedures t1
      FULL OUTER JOIN '
    + @DB2 + '.sys.procedures t2
      on t1.name = t2.name
      where t1.object_id IS NULL
      OR t2.object_id IS NULL')

Junket

Answer 10

Not enough reputation to comment, so here's a new answer for you. I used the code provided by John Moore, but added and C1.colid = C2.colid to compare procedures that span multiple entries. Here's the full code:

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 C1.colid = C2.colid and
-- remove the line below if you want to search all objects
O1.type = 'P' 

Jecon

Answer 11

The following Procedure can find out the differences of Functions, Procedures, Triggers in two different databases. Pass the database names as parameters.

IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE Specific_name = 'SP_SCRIPTDIFF')

DROP PROCEDURE DBO.SP_SCRIPTDIFF

GO

CREATE PROCEDURE [dbo].SP_SCRIPTDIFF

@DBNAME1 SYSNAME, 
@DBNAME2 SYSNAME

AS

/*
DATE      : 2016/07/29
AUTHOR    : SEENI 
OBJECTIVE : TO COMPARE THE FUNCTIONS, PROCEDURES AND TRIGGERS IN TWO DIFFERENT DATABASES, PASS NAME OF DATABASE1, AND DATABASE2 AS INPUTS.
*/

BEGIN

SET NOCOUNT ON 

Exec ('select  DISTINCT O1.name as [ObjectName], O1.modify_date As DateIn_'+@DBNAME1+',  O2.modify_date As DateIn_'+@DBNAME2+',o1.type as Type from '+
@DBNAME1+'.sys.all_objects O1 join '+ @DBNAME2+'.sys.all_objects O2 on O1.name = O2.name and  O1.type = O2.type join '+
@DBNAME1+'.sys.syscomments C1 on O1.object_id = C1.id join '+ @DBNAME2+'.sys.syscomments C2 on O2.object_id = C2.id join '+
@DBNAME1+'.sys.schemas S1 on O1.schema_id = S1.schema_id join '+ @DBNAME2+'.sys.schemas S2 on O2.schema_id = S2.schema_id
where C1.text <> C2.text and c1.colid = c2.colid  and O1.Type in (''FN'',''P'',''T'') And o1.Is_Ms_Shipped = 0  Order by O1.type,ObjectName')

RETURN

END 

GO

Shiksa