Script to compare object code between two Production and development databases

Leave a comment (0) Go to comments

Question : How to compare SQL Server Development and production boxes (two databases) in terms of object definition like

  • Stored Procedures
  • Triggers
  • Views
  • Function

OR

SQL Script to indentify databases objects where definition is not same.

Solution

This is a stored procedure to compare the code of the objects (Stored Procedures , Triggers , Views and Function) between two databases.

This will be helpful to generate a report on which objects are out of synch. This uses the system table SYS.SQL_modules.

IF OBJECT_ID('CompareObjectCode','P') IS NOT NULL
    DROP PROC CompareObjectCode 

GO

CREATE PROC CompareObjectCode
 @SourceDBName SYSNAME       
,@DestDBName   SYSNAME     
,@ObjectName   SYSNAME     = '%'

AS
/* Parameters

 @SourceDBName = Source Database Name 
 @DestDBName   = Destination Database Name with which the code has to be compared
 @ObjectName   = For comparing the required object names only. This parameter accepts wildcard LIKE also.
                 Default '%' = All Objects
                  
*/


    DECLARE @SQL VARCHAR(MAX)

    SELECT @Sql = 
    '
    SELECT  ''Object Name'' = ISNULL(SRC.name,Dest.name)
           ,''Object Type'' = ISNULL(SRC.type_desc,Dest.type_desc)
           ,''Status'' =   CASE
                                WHEN SRC.Definition is null THEN ''Missing in Source''
                                WHEN Dest.Definition is null THEN ''Missing in Destination''
                                WHEN SRC.Definition <> Dest.Definition THEN ''Definition Mismatch''
                                ELSE ''Match''
                                END
           ,''Source Created Date'' = SRC.create_date
           ,''Source Modify Date''  = SRC.modify_date
           ,''Destination Created Date'' = Dest.create_date
           ,''Destination Modify Date''  = Dest.modify_date
      FROM 
        (
         SELECT so.name,so.create_date,so.modify_date,sm.definition ,so.type_desc
           FROM ' + @SourceDBName + '.SYS.objects so
           JOIN ' + @SourceDBName + '.SYS.sql_modules sm
             ON so.object_id = sm.object_id
          WHERE so.name like ''' + @ObjectName + '''
        ) Src
        FULL OUTER JOIN
        (
         SELECT so.name,so.create_date,so.modify_date,sm.definition ,so.type_desc
           FROM ' + @DestDBName + '.SYS.objects so
           JOIN ' + @DestDBName + '.SYS.sql_modules sm
             ON so.object_id = sm.object_id
          WHERE so.name like ''' + @ObjectName + '''
        ) Dest
        ON SRC.name = dEST.name
    '

    EXEC (@Sql)

GO

Modify the above script parameters to meet your requirement.


Parameters

 @SourceDBName = Source Database Name

 @DestDBName    = Destination Database Name with which the code has to be compared

 @ObjectName     = For comparing the required object names only. This parameter accepts wildcard LIKE also. Default ‘%’.All Objects

Usage

  • To compare the code for all the objects
EXEC CompareObjectCode @SourceDBName='<SourceDB>',@DestDBName='<DestDB>' 
  • To compare the code for the objects having a specfic pattern in the name. The below does a comparision for the objects containing the leters "sp"
    EXEC CompareObjectCode @SourceDBName='<SourceDB>',@DestDBName='<DestDB>',@objectname = '%sp%'
EOF - Script to compare object code between two Production and development databases, SQL Server 2012 , 2008 R2 , 2008 , 2005 , 2000

Leave a Reply

Your email address will not be published. Required fields are marked *


*


You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Notify me of followup comments via e-mail. You can also subscribe without commenting.