比较两个数据库的存储过程定义是否一致V1.0

本脚本适用于Microsoft SQL Server 2005和Microsoft SQL Server 2008环境。

 

Use master
Go
if object_ID('[sp_compareobjectdefinition]') is not null
    Drop Procedure [sp_compareobjectdefinition]
Go
/*
    比较两个数据库的存储过程定义是否一致V1.0 OK_008 2009-5-26
*/
Create Procedure sp_CompareObjectDefinition
(
    @DB1 nvarchar(512),
    @DB2 nvarchar(512),
    @Type char(2)='P',
    @ObjectName  nvarchar(512)=null
)
As
    /*
    参数说明
    
    @DB1            数据库名
    @DB2            数据库名
    @Type            对象类型,参照sys.objects中的type列描述
    @ObjectName        对象名称
 
    */
    Set Nocount On
 
    If @DB1=@DB2
    Begin
        Raiserror 50001 N'@DB1,@DB2设置不能相同。'
        Return(1)
    End
 
    If db_id(@DB1) Is Null
    Begin
        Raiserror 50001 N'无效的数据库名,@DB1设置错误。'
        Return(1)
    End
 
    If db_id(@DB2) Is Null
    Begin
        Raiserror 50001 N'无效的数据库名,@DB2设置错误。'
        Return(1)
    End
    
    Set @Type=Isnull(@Type,' P')
    
    Declare @sql nvarchar(4000),
            @sql1 nvarchar(1000),
            @where nvarchar(1000)
 
    If object_id('tempdb..#ObjectDefinition') Is Not Null
        Drop Table #ObjectDefinition
    
    Create Table #ObjectDefinition
    (
        dbname sysname,
        name sysname,
        type char(2),
        definition nvarchar(max)
    )
    
    Set @where=''
    If @Type >''        
        Set @where=' And a.Type='''+@Type+''''
 
    If @ObjectName >''    
        Set @where=@where+' And a.name='''+@ObjectName+''''
 
    
    Set @sql=N'
    a.name,a.type,b.definition
        From sys.objects As a
            Inner Join sys.sql_modules As b On b.object_id=a.object_id
        Where a.is_ms_shipped=0  '+@where+' And
                Not Exists(Select 1 From sys.extended_properties Where major_id=a.object_id And minor_id=0 And class=1 And name = N''microsoft_database_tools_support'')'
    Set @sql1='Use '+Quotename(@DB1)+'; Select dbname='''+@DB1+''','
 
    Insert Into #ObjectDefinition Exec(@sql1+@sql)
    
    Set @sql1='Use '+Quotename(@DB2)+'; Select dbname='''+@DB2+''','
    Insert Into #ObjectDefinition Exec(@sql1+@sql)
 
    Exec(N'
    ;With t As
    (
        Select name,Type From (Select name,Type,Definition From #ObjectDefinition Where dbname='''+@DB1+'''    Except Select name,Type,Definition From #ObjectDefinition Where dbname='''+@DB2+''') As a
        Union All
        Select name,Type From (Select name,Type,Definition From #ObjectDefinition Where dbname='''+@DB2+'''    Except Select name,Type,Definition From #ObjectDefinition Where dbname='''+@DB1+''') As b
    )
    Select Distinct a.Name As ObjectName,a.Type,
            Case When b.Name Is Not Null Then 1 Else 0 End As ['+@DB1+'],
            Case When c.Name Is Not Null Then 1 Else 0 End As ['+@DB2+']
        From t As a
            Left Outer Join #ObjectDefinition As b On b.name=a.name And b.DBName='''+@DB1+'''
            Left Outer Join #ObjectDefinition As c On c.name=a.name And c.DBName='''+@DB2+'''

        Order By a.Type,a.Name
    ')

    Drop Table #ObjectDefinition
 
Go