-
-- 比较两个数据库中表的差异
-- 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
扫一扫
1万+
评论
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
查看更多评论
添加红包
