现在想比较他们的字段名及字段个数是否相同。
sql语句可以吗
手工比较太麻烦了，要比较的表太多了
------解决方案--------------------
- SQL code
-- 比较两个数据库中表的差异-- u表，p存储过程,v视图-- INTFSIMSNEW新库，INTFSIMS旧库SELECT NTABLE = A.NAME, OTABLE = B.NAMEFROM INTFSIMSNEW..SYSOBJECTS A LEFT JOIN INTFSIMS..SYSOBJECTS B ON A.NAME = B.NAMEWHERE ISNULL(B.NAME, '') = '' AND A.XTYPE = 'U'UNION ALLSELECT NTABLE = B.NAME, OTABLE = A.NAMEFROM INTFSIMS..SYSOBJECTS A LEFT JOIN INTFSIMSNEW..SYSOBJECTS B ON A.NAME = B.NAMEWHERE 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) ENDFROM (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.FIELDNAMEWHERE 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.ALLOWNULLORDER by 1, 4