在有些时候, 需要知道测试站和正式站的数据库有那些不同, 如果单纯用肉眼去检查,那几乎是不可能完成的事情。这个时候我们就需要借助一些系统内置的表来进行关联查询了。
--第一个库
DECLARE @db1 sysname = 'Construction_Demo';
--第二个库
DECLARE @db2 sysname = 'Construction_Test';
DECLARE @sqlStr VARCHAR(MAX) = '';
SET @sqlStr = '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 ,
(
SELECT b.text + ''''
FROM ' + @db1 + '..syscomments b
WHERE a.id = b.id
ORDER BY b.colid
FOR XML PATH('''') ,
TYPE).value(''.'', ''NVARCHAR(MAX)'') AS text
FROM ' + @db1 + '..sysobjects a
WHERE a.xtype IN ( ''V'', ''P'' )
AND a.status >= 0
) a
FULL JOIN ( SELECT a.name ,
a.xtype ,
(
SELECT b.text + ''''
FROM ' + @db2 + '..syscomments b
WHERE a.id = b.id
ORDER BY b.colid
FOR XML PATH('''') ,
TYPE).value(''.'', ''NVARCHAR(MAX)'') AS text
FROM ' + @db2 + '..sysobjects a
WHERE a.xtype IN ( ''V'', ''P'' )
AND a.status >= 0
) b ON a.name = b.name
AND a.xtype = b.xtype
WHERE a.name IS NULL
OR b.name IS NULL
OR ISNULL(a.text, '''') <> ISNULL(b.text, '''')';
EXEC(@sqlStr);
执行结果:
这样就完美解决了手动检查的痛点!