Technical Article

Find Mismatched Views and Stored Procs

,

Hi,

Sometimes during development, stored procs and views are modified or added. These additions dont always follow through to the staging environment, causing the Application to crash or recover gracefully.

I use this stored procs to find these mismatches and missing objects. On my SQL Box this stored proc has been created in the master database, but it could be created in any database. Execution is simple it is:

exec sp_utl_FindMisMatchedObjects 'FirstdatabaseName','SecondDatabasename'

Thought I would share this code in thanks for the many times I have been helped out on this site.

Pratap

Note: Due to the tabs I use during development I find that copying and pasting from sqlserverCentral web page to QueryAnalyzer gives a number of errors, instead paste to Notepad, replace the control characters with spaces using ctrl+h and then paste to Query Analyzer.

Create proc [dbo].[sp_utl_FindMisMatchedObjects]
(    @strDbName1 varchar(20)=null
    ,@strDbName2 varchar(20)=null
)
as
BEGIN
    /*
        Author : Pratap J Prabhu
        Date : 1/10/2008
        Description : - Check consistency of views and 
                    stored procs between 2 databases.
                  - Used to find missing and mismatched 
                    stored procs and views between 2 
                    databases
    Usage : exec master.dbo.sp_utl_FindMisMatchedObjects 'DevDB', 'StageDB' 
    */    set nocount on
    declare @iRetVal int
    set @iRetVal=0
    if(@strDbName1 is null or @strDbname2 is null)
    begin
        print 'Syntax : exc sp_utl_FindMisMatchedObjects ''DBName'',''DBName2'''
        set @iRetVal=1
    end
    if(not exists(select * from sys.databases WHERE name =@strDBName1)
 or not exists(select * from sys.databases WHERE name =@strDBName2)
 )
    begin
        print 'Syntax : exc sp_utl_FindMisMatchedObjects ''DBName'',''DBName2'''
        set @iRetVal=1
    end
    if(@strDBName1=@strDBName2)
    begin
        print 'Syntax : exc sp_utl_FindMisMatchedObjects ''DBName'',''DBName2'''
        set @iRetVal=1
    end
    if(@iRetval=0)
    begin
        declare @tblDbObjects
            table
            ( 
                DBName                    varchar(20),
                ObjectType VARCHAR(20), 
                ObjectName VARCHAR(500), 
                CreatedOn                DATETIME,
                ModifiedOn                DATETIME,
                Script VARCHAR(MAX) 
            )

        declare @SqlProc1 varchar(MAX)
        declare @SqlView1 varchar(MAX)
        declare @SqlProc2 varchar(MAX)
        declare @SqlView2 varchar(MAX)

        select @SqlProc1 = null
                ,@SqlView1 = null
                ,@SqlProc2 = null
                ,@SqlView2 = null
        set @SqlProc1=' SELECT DbName            =''' +ltrim(rtrim(@strDbName1))+''''
                            +',ObjectType    =''Stored Procedure'''
                            +',ObjectName    =P.name'
                            +',CreatedOn    =P.create_date'
                            +',ModifiedOn    =P.modify_date'
                            +',Script        =ltrim(rtrim(M.definition))'
                    + ' FROM '+ltrim(rtrim(@strDbName1))+'.sys.sql_modules M'
                        +' INNER JOIN '+ltrim(rtrim(@strDbName1))+'.sys.procedures P'
                            +' ON M.object_id = P.object_id;'
        set @SqlProc2=' SELECT DbName            =''' +ltrim(rtrim(@strDbName2))+''''
                            +',ObjectType    =''Stored Procedure'''
                            +',ObjectName    =P.name'
                            +',CreatedOn    =P.create_date'
                            +',ModifiedOn    =P.modify_date'
                            +',Script        =ltrim(rtrim(M.definition))'
                    + ' FROM '+ltrim(rtrim(@strDbName2))+'.sys.sql_modules M'
                        +' INNER JOIN '+ltrim(rtrim(@strDbName2))+'.sys.procedures P'
                            +' ON M.object_id = P.object_id;'


        set @SqlView1=' SELECT DbName            =''' +ltrim(rtrim(@strDbName1))+''''
                            +',ObjectType    =''View'''
                            +',ObjectName    =v.name'
                            +',CreatedOn    =v.create_date'
                            +',ModifiedOn    =v.modify_date'
                            +',Script        =ltrim(rtrim(M.definition))'
                    + ' FROM '+ltrim(rtrim(@strDbName1))+'.sys.sql_modules M'
                        +' INNER JOIN '+ltrim(rtrim(@strDbName1))+'.sys.views V'
                            +' ON M.object_id = v.object_id;'
                    
        set @SqlView2=+' SELECT DbName            =''' +ltrim(rtrim(@strDbName2))+''''
                            +',ObjectType    =''View'''
                            +',ObjectName    =v.name'
                            +',CreatedOn    =v.create_date'
                            +',ModifiedOn    =v.modify_date'
                            +',Script        =ltrim(rtrim(M.definition))'
                    + ' FROM '+ltrim(rtrim(@strDbName2))+'.sys.sql_modules M'
                        +' INNER JOIN '+ltrim(rtrim(@strDbName2))+'.sys.views V'
                            +' ON M.object_id = v.object_id;'


        insert into @tblDbObjects    exec (@SqlProc1)
        insert into @tblDbObjects    exec (@SqlProc2)
        insert into @tblDbObjects     exec (@SqlView1)
        insert into @tblDbObjects     exec (@SqlView2)


        --select * from @tblDbObjects
        select 'Objects Missing from ' + @strDbName2
                , DBName1        =db1.DBName
                , ObjectType    =db1.ObjectType
                , CreatedOn1    =db1.CreatedOn
                , ModifiedOn1    =db1.ModifiedOn
                , DBName2        =db2.DBName
                , CreatedOn2    =db2.CreatedOn
                , ModifiedOn2    =db2.ModifiedOn
                , Script1        =db1.Script
                , Script2        =db2.Script
        from @tblDbObjects db1
            left join @tblDbObjects db2
                on db1.DBname<>db2.DbName
                 and db1.ObjectType=db2.ObjectType
                    and db1.ObjectName=db2.ObjectName
        where db1.DbName=@strDbName1
                and db2.ObjectName is null

        union all 

        select 'Objects Missing from ' + @strDbName1
                , DBName1        =db1.DBName
                , ObjectType    =db1.ObjectType
                , CreatedOn1    =db1.CreatedOn
                , ModifiedOn1    =db1.ModifiedOn
                , DBName2        =db2.DBName
                , CreatedOn2    =db2.CreatedOn
                , ModifiedOn2    =db2.ModifiedOn
                , Script1        =db1.Script
                , Script2        =db2.Script
        from @tblDbObjects db1
            left join @tblDbObjects db2
                on db1.DBname<>db2.DbName
                 and db1.ObjectType=db2.ObjectType
                    and db1.ObjectName=db2.ObjectName
        where db1.DbName=@strDbName2
                and db2.ObjectName is null

        union all 

        select 'Mismatched Objects between ' + @strDbName1 + ' and ' + @strDbName2
                , DBName1        =db1.DBName
                , ObjectType    =db1.ObjectType
                , CreatedOn1    =db1.CreatedOn
                , ModifiedOn1    =db1.ModifiedOn
                , DBName2        =db2.DBName
                , CreatedOn2    =db2.CreatedOn
                , ModifiedOn2    =db2.ModifiedOn
                , Script1        =db1.Script
                , Script2        =db2.Script
        from @tblDbObjects db1
            join @tblDbObjects db2
                on db1.DBname<>db2.DbName
                 and db1.ObjectType=db2.ObjectType
                 and db1.ObjectName=db2.ObjectName
        where db1.DbName=@strDbName1
                and ltrim(rtrim(replace(replace(db1.Script,char(10),' '),char(13),' ')))<>ltrim(rtrim(replace(replace(db2.Script,char(10),' '),char(13),' ')))

    end
    return @iRetVal
END
go

Rate

4 (5)

You rated this post out of 5. Change rating

Share

Share

Rate

4 (5)

You rated this post out of 5. Change rating