同服务器 内的 不同数据库对象之间的对比

以下 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

 

posted @ 2018-08-24 12:29  ^ν^向上人生^ν^  阅读(172)  评论(0)  编辑  收藏  举报