SQL SERVER 比较两个数据库的差异性「建议收藏」

小小码 • 2023-05-19 19:30 • 代码基础 • 阅读 146

大家好，我是考100分的小小码，祝大家学习进步，加薪顺利呀。今天说一说SQL SERVER 比较两个数据库的差异性「建议收藏」,希望您对编程的造诣更进一步.

SQL SERVER 比较两个数据库的差异性

有时候部署服务器正式版与测试版数据库的时候

总会有忘记某些字段同步更新的问题

不管是字段类型或是字段名称. 然后等待着的就是一堆的错误日志.

所以一直想找一款可以查找数据库差异性的脚本或软件

运行以下SQL语句，查看数据库差异性

-- u表，p存储过程,v视图
-- INTFSIMSNEW新库，INTFSIMS旧库

SELECT NTABLE = A.NAME, OTABLE = B.NAME
FROM INTFSIMSNEW..SYSOBJECTS A
  LEFT JOIN INTFSIMS..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 INTFSIMS..SYSOBJECTS A
  LEFT JOIN INTFSIMSNEW..SYSOBJECTS B
    ON A.NAME = B.NAME
WHERE ISNULL(B.NAME, "") = ""
  AND A.XTYPE = "U"
ORDER BY 1, 2

-- 比较两个数据库中每个表字段的差异
SELECT
  表名A = CASE WHEN ISNULL(A.TABLENAME, "") <> "" THEN A.TABLENAME ELSE B.TABLENAME END,
  字段名A = A.FIELDNAME,
  字段名B = B.FIELDNAME,
  顺序= A.FIELDSNO,
  说明= CASE WHEN A.FIELDTYPE <> B.FIELDTYPE THEN "类型: " + A.FIELDTYPE + "-->" + B.FIELDTYPE
              WHEN A.FIELDSNO <> B.FIELDSNO THEN "顺序: " + str(A.FIELDSNO) + "-->" + str(B.FIELDSNO)
              WHEN A.LENGTH <> B.LENGTH THEN "长度: " + str(A.LENGTH) + "-->" + str(B.LENGTH)
              WHEN A.LENSEC <> B.LENSEC THEN "小数位: " + str(A.LENSEC) + "-->" + str(B.LENSEC)
              WHEN A.ALLOWNULL <> B.ALLOWNULL THEN "允许空值: " + str(A.ALLOWNULL) + "-->" + str(B.ALLOWNULL)
         END
FROM (SELECT
        TABLENAME = B.NAME,
        FIELDNAME = A.NAME,
        FIELDSNO = A.COLID,
        FIELDTYPE = C.NAME,
        LENGTH = A.LENGTH,
        LENSEC = A.XSCALE,
        ALLOWNULL = A.ISNULLABLE
      FROM INTFSIMSNEW..SYSCOLUMNS A
        LEFT JOIN INTFSIMSNEW..SYSOBJECTS B
          ON A.ID = B.ID
        LEFT JOIN INTFSIMSNEW..SYSTYPES C
          ON A.XUSERTYPE = C.XUSERTYPE
      WHERE B.XTYPE = "U") A
  FULL JOIN (SELECT
               TABLENAME = B.NAME,
               FIELDNAME = A.NAME,
               FIELDSNO = A.COLID,
               FIELDTYPE = C.NAME,
               LENGTH = A.LENGTH,
               LENSEC = A.XSCALE,
               ALLOWNULL = A.ISNULLABLE
             FROM INTFSIMS..SYSCOLUMNS A
               LEFT JOIN INTFSIMS..SYSOBJECTS B
                 ON A.ID = B.ID
               LEFT JOIN INTFSIMS..SYSTYPES C
                 ON A.XUSERTYPE = C.XUSERTYPE
             WHERE B.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

原文地址：https://www.cnblogs.com/kangsir7/archive/2022/05/31/16330287.html

版权声明：本文内容由互联网用户自发贡献，该文观点仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容，请发送邮件至举报，一经查实，本站将立刻删除。
转载请注明出处: https://daima100.com/5181.html

SQL SERVER 比较两个数据库的差异性「建议收藏」

相关推荐

发表回复