以下 SQL 只适用于 同服务器 内的 不同数据库 之间的对比，如果是不同的服务器，可以把 服务器A 中的目标数据库备份后，以另一个名字恢复到 服务器B 中
1、u表，p存储过程，v视图 的存在性对比
1）表
sysobjects.uid 用来区分 dbo.XXX 和 b2berp.XXX （table 创建引起的不同）
SELECT
TABLE_YC3 = A.name2, TABLE_YC = B.name2
FROM
(SELECT U.name+'.'+O.name AS name2, O.*
FROM B2BERP_YC3..sysobjects AS O
LEFT JOIN B2BERP_YC3..sysusers AS U ON O.uid = U.uid) A
LEFT JOIN
(SELECT U.name+'.'+O.name AS name2, O.*
FROM B2BERP_YC..sysobjects AS O
LEFT JOIN B2BERP_YC..sysusers AS U ON O.uid = U.uid) B
ON A.name2 = B.name2
WHERE ISNULL(B.name2, '') = '' -- 只在 YC3 里存在的表
AND A.xtype = 'U'
UNION
SELECT
TABLE_YC3 = B.name2, TABLE_YC = A.name2
FROM
(SELECT U.name+'.'+O.name AS name2, O.*
FROM B2BERP_YC..sysobjects AS O
LEFT JOIN B2BERP_YC..sysusers AS U ON O.uid = U.uid) A
LEFT JOIN
(SELECT U.name+'.'+O.name AS name2, O.*
FROM B2BERP_YC3..sysobjects AS O
LEFT JOIN B2BERP_YC3..sysusers AS U ON O.uid = U.uid) B
ON A.name2 = B.name2
WHERE ISNULL(B.name2, '') = '' -- 只在 YC 里存在的表
AND A.xtype = 'U'
ORDER BY 1, 2
2）存储过程
SELECT
TABLE_YC3 = A.name2, TABLE_YC = B.name2
FROM
(SELECT U.name+'.'+O.name AS name2, O.*
FROM B2BERP_YC3..sysobjects AS O
LEFT JOIN B2BERP_YC3..sysusers AS U ON O.uid = U.uid) A
LEFT JOIN
(SELECT U.name+'.'+O.name AS name2, O.*
FROM B2BERP_YC..sysobjects AS O
LEFT JOIN B2BERP_YC..sysusers AS U ON O.uid = U.uid) B
ON A.name2 = B.name2
WHERE ISNULL(B.name2, '') = '' -- 只在 YC3 里存在的存储过程
AND A.xtype = 'P'
UNION
SELECT
TABLE_YC3 = B.name2, TABLE_YC = A.name2
FROM
(SELECT U.name+'.'+O.name AS name2, O.*
FROM B2BERP_YC..sysobjects AS O
LEFT JOIN B2BERP_YC..sysusers AS U ON O.uid = U.uid) A
LEFT JOIN
(SELECT U.name+'.'+O.name AS name2, O.*
FROM B2BERP_YC3..sysobjects AS O
LEFT JOIN B2BERP_YC3..sysusers AS U ON O.uid = U.uid) B
ON A.name2 = B.name2
WHERE ISNULL(B.name2, '') = '' -- 只在 YC 里存在的存储过程
AND A.xtype = 'P'
ORDER BY 1, 2
3）SELECT
TABLE_YC3 = A.name2, TABLE_YC = B.name2
FROM
(SELECT U.name+'.'+O.name AS name2, O.*
FROM B2BERP_YC3..sysobjects AS O
LEFT JOIN B2BERP_YC3..sysusers AS U ON O.uid = U.uid) A
LEFT JOIN
(SELECT U.name+'.'+O.name AS name2, O.*
FROM B2BERP_YC..sysobjects AS O
LEFT JOIN B2BERP_YC..sysusers AS U ON O.uid = U.uid) B
ON A.name2 = B.name2
WHERE ISNULL(B.name2, '') = '' -- 只在 YC3 里存在的视图
AND A.xtype = 'V'
UNION
SELECT
TABLE_YC3 = B.name2, TABLE_YC = A.name2
FROM
(SELECT U.name+'.'+O.name AS name2, O.*
FROM B2BERP_YC..sysobjects AS O
LEFT JOIN B2BERP_YC..sysusers AS U ON O.uid = U.uid) A
LEFT JOIN
(SELECT U.name+'.'+O.name AS name2, O.*
FROM B2BERP_YC3..sysobjects AS O
LEFT JOIN B2BERP_YC3..sysusers AS U ON O.uid = U.uid) B
ON A.name2 = B.name2
WHERE ISNULL(B.name2, '') = '' -- 只在 YC 里存在的视图
AND A.xtype = 'V'
ORDER BY 1, 2
4）综合（表，存储过程，视图）
SELECT TABLE_YC3 = A.NAME, TABLE_YC = B.NAME
FROM B2BERP_YC3..SYSOBJECTS A
LEFT JOIN B2BERP_YC..SYSOBJECTS B
ON A.NAME = B.NAME
WHERE ISNULL(B.NAME, '') = ''
AND A.XTYPE = 'U'
UNION ALL
SELECT NTABLE = B.NAME, OTABLE = A.NAME
FROM B2BERP_YC..SYSOBJECTS A
LEFT JOIN B2BERP_YC3..SYSOBJECTS B
ON A.NAME = B.NAME
WHERE ISNULL(B.NAME, '') = ''
AND A.XTYPE = 'U'
ORDER BY 1, 2
2、 比较两个数据库的 视图/存储过程 的结构差异
-- syscomments 记录了 SQL 定义语句的实际文本，超过 4000个 字符的，会分行并通过 syscomments.colid 区分
-- Notice: 要确定具体差异，需要对 视图/存储过程 的定义文本进行对比（多余的空行、空格也会造成文本不同）
SELECT
类型=CASE ISNULL(A.xtype,B.xtype) WHEN 'V' THEN N'视图' ELSE N'存储过程' END
,匹配情况=CASE
WHEN A.name IS NULL THEN N'库 B2BERP_YC3 中无'
WHEN B.name IS NULL THEN N'库 B2BERP_YC 中无'
ELSE N'结构不同' END
,对象名称=ISNULL(A.name,B.name)
,A.text AS text_YC3
,B.text AS text_YC
FROM
(SELECT O.name, O.xtype, C.colid, C.text
FROM B2BERP_YC3..sysobjects AS O, B2BERP_YC3..syscomments AS C
WHERE O.id = C.id AND O.xtype IN ('V','P') AND O.status >= 0) A
FULL JOIN
(SELECT O.name, O.xtype, C.colid, C.text
FROM B2BERP_YC..sysobjects AS O, B2BERP_YC..syscomments AS C
WHERE O.id = C.id AND O.xtype IN ('V','P') AND O.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,'')
ORDER BY 1, 2, 3
3、比较两个数据库中每个表字段的差异
SELECT
表名 = CASE WHEN ISNULL(A.TABLENAME, '') <> '' THEN A.TABLENAME ELSE B.TABLENAME END,
字段名_YC3 = A.FIELDNAME,
字段名_YC = B.FIELDNAME,
顺序 = A.FIELDSNO,
说明 = CASE WHEN A.FIELDTYPE <> B.FIELDTYPE THEN N'类型: ' + A.FIELDTYPE + '-->' + B.FIELDTYPE
WHEN A.FIELDSNO <> B.FIELDSNO THEN N'顺序: ' + STR(A.FIELDSNO) + '-->' + STR(B.FIELDSNO)
WHEN A.LENGTH <> B.LENGTH THEN N'长度: ' + STR(A.LENGTH) + '-->' + STR(B.LENGTH)
WHEN A.LENSEC <> B.LENSEC THEN N'小数位: ' + STR(A.LENSEC) + '-->' + STR(B.LENSEC)
WHEN A.ALLOWNULL <> B.ALLOWNULL THEN N'允许空值: ' + STR(A.ALLOWNULL) + '-->' + STR(B.ALLOWNULL)
END
FROM
(SELECT
TABLENAME = O.name
,FIELDNAME = C.name
,FIELDSNO = C.colid
,FIELDTYPE = T.name
,LENGTH = C.prec
,LENSEC = C.scale
,ALLOWNULL = C.isnullable
FROM B2BERP_YC3..syscolumns AS C
LEFT JOIN B2BERP_YC3..sysobjects AS O ON C.id = O.id
LEFT JOIN B2BERP_YC3..systypes T ON C.xusertype = T.xusertype
WHERE O.xtype = 'U') A
FULL JOIN
(SELECT
TABLENAME = O.name
,FIELDNAME = C.name
,FIELDSNO = C.colid
,FIELDTYPE = T.name
,LENGTH = C.prec
,LENSEC = C.scale
,ALLOWNULL = C.isnullable
FROM B2BERP_YC..syscolumns AS C
LEFT JOIN B2BERP_YC..sysobjects AS O ON C.id = O.id
LEFT JOIN B2BERP_YC..systypes T ON C.xusertype = T.xusertype
WHERE O.xtype = 'U') B
ON A.TABLENAME = B.TABLENAME
AND A.FIELDNAME = B.FIELDNAME
WHERE ISNULL(A.TABLENAME, '') = ''
OR ISNULL(B.TABLENAME, '') = ''
OR A.FIELDTYPE <> B.FIELDTYPE
OR A.FIELDSNO <> B.FIELDSNO
OR A.LENGTH <> B.LENGTH
OR A.LENSEC <> B.LENSEC
OR A.ALLOWNULL <> B.ALLOWNULL
ORDER by 1, 4