SQL Server 比較兩個資料庫的檢視和儲存過程結構差異

IF EXISTS ( SELECT  *
            FROM    dbo.sysobjects
            WHERE   id = OBJECT_ID(N'[dbo].[p_compdb]')
                    AND OBJECTPROPERTY(id, N'IsProcedure') = 1 )
    DROP PROCEDURE [dbo].[p_comparestructure] 
GO 

/*--呼叫示例 比較兩個資料庫的 檢視 儲存過程 結構差異
exec p_compdb 'DBName1','DBName2' 
--*/
ALTER PROC p_compdb
    @db1 SYSNAME , --第一個庫 
    @db2 SYSNAME --第二個庫 
AS
    EXEC(' 
    select 型別=case isnull(a.xtype,b.xtype) when ''V'' then ''檢視'' else ''儲存過程'' end 
    ,匹配情況=case 
    when a.name is null then ''庫 ['+@db1+'] 中無'' 
    when b.name is null then ''庫 ['+@db2+'] 中無'' 
    else ''結構不同'' end 
    ,物件名稱=isnull(a.name,b.name),a.text as atext, b.text as btext
    from( 
    select a.name,a.xtype,b.colid,b.text 
    from ['+@db1+']..sysobjects a,['+@db1+']..syscomments b 
    where a.id=b.id and a.xtype in(''V'',''P'') and a.status>=0 
    )a full join( 
    select a.name,a.xtype,b.colid,b.text 
    from ['+@db2+']..sysobjects a,['+@db2+']..syscomments b 
    where a.id=b.id and a.xtype in(''V'',''P'') and a.status>=0 
    )b on a.name=b.name and a.xtype=b.xtype and a.colid=b.colid 
    where a.name is null 
    or b.name is null 
    or isnull(a.text,'''') <>isnull(b.text,'''') 
    --group by a.name,b.name,a.xtype,b.xtype 
    --order by 型別,匹配情況,物件名稱')