2021-07-15 MYSQL比较两个数据库中表和字段的差异

– 比较两个数据库中表的差异
– 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