比较两个数据库的不同

由于正式库与测试库有些改动未能同步修改，导致结构上有不同，安装了数据库比较工具，可是不怎么好用，而且正版需要付费，试用版功能又不全，搜索stackoverflow时发现了这个，感觉简单好用，自己之前怎么就没想到呢。

--比较两个数据库的不同
DECLARE @Sourcedb sysname 
DECLARE @Destdb sysname 
DECLARE @SQL varchar(max) 

SELECT @Sourcedb = 'AAA' 
SELECT @Destdb = 'BBB' 

SELECT @SQL = ' SELECT ISNULL(SoSource.name,SoDestination.name) ''Object Name'' ,
                CASE WHEN SoSource.object_id IS NULL THEN SoDestination.type_desc +  '' source库中不存在 -- ' + @Sourcedb + ''' COLLATE database_default 
                     WHEN SoDestination.object_id IS NULL THEN SoSource.type_desc +  '' Destination库中不存在 -- ' + @Destdb + ''' COLLATE database_default 
                     ELSE SoDestination.type_desc + '' 双方一致'' COLLATE database_default END ''Status'' 
                FROM (  SELECT  *
                        FROM ' + @Sourcedb + '.SYS.objects  
                        WHERE   Type_desc not in (''INTERNAL_TABLE'',''SYSTEM_TABLE'',''SERVICE_QUEUE'')) SoSource FULL OUTER JOIN
                     (  SELECT * 
                        FROM    ' + @Destdb + '.SYS.objects  
                        WHERE   Type_desc not in (''INTERNAL_TABLE'',''SYSTEM_TABLE'',''SERVICE_QUEUE'')) SoDestination ON SoSource.name = SoDestination.name COLLATE database_default AND SoSource.type = SoDestination.type COLLATE database_default 
                WHERE   SoSource.object_id IS NULL OR SoDestination.object_id IS NULL
                ORDER BY isnull(SoSource.type,SoDestination.type)' 
EXEC (@Sql)

发现sys.objects表中不包含索引，于是再写了一个查询sys.indexes的，具体需要哪些字段还需要修改。

DECLARE @SQL VARCHAR(max)
DECLARE @ADB sysname
DECLARE @BDB sysname

SET @ADB = 'AAA'
SET @BDB = 'BBB'

SET @SQL = 'SELECT  CASE WHEN A.object_id IS NULL THEN ''' + @BDB + ''' + CONVERT(VARCHAR(100),B.object_id) + ''' ''' + B.name
            WHEN B.object_id IS NULL THEN ''' + @ADB + ''' + CONVERT(VARCHAR(100),A.object_id) + A.name
            ELSE A.name + '''双方一致''' END AS Result
    FROM    ''' + @ADB + '''.SYS.indexes A FULL OUTER JOIN
            ''' + @BDB + '''.SYS.indexes B ON A.name = B.name
    WHERE   A.object_id IS NULL
            OR B.object_id IS NULL'
EXEC (@Sql)