-- 比较两个数据库中表的差异
-- 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://blog.csdn.net/netuser1937/article/details/53782958?locationNum=1&fps=1