日日操夜夜添-日日操影院-日日草夜夜操-日日干干-精品一区二区三区波多野结衣-精品一区二区三区高清免费不卡

有時候部署服務(wù)器正式版與測試版數(shù)據(jù)庫的時候

總會有人忘記某些字段同步更新的問題

不管是字段類型 或是字段名稱. 然后等待著的就是一堆的錯誤日志.

所以一直想找一款可以查找數(shù)據(jù)庫差異性的腳本或軟件

運行以下SQL語句，查看數(shù)據(jù)庫差異性

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

-- 比較兩個數(shù)據(jù)庫中每個表字段的差異
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 '小數(shù)位: ' + 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