DM8:达梦数据库多个库之间的表结构对比

1 创建比对表 TAB_DIFF

CREATE TABLE SYSDBA.TAB_DIFF(SOURCE VARCHAR2(128),OWNER VARCHAR2(128),TABLE_NAME VARCHAR2(128),COLUMN_NAME VARCHAR2(128),DATA_TYPE VARCHAR2(32),DATA_LENGTH VARCHAR2(16),COLUMN_ID INT);
1

2 查询数据源 A1的表结构

SELECT 'A1' AS SOURCE,OWNER,TABLE_NAME,COLUMN_NAME,DATA_TYPE,DATA_LENGTH,COLUMN_ID FROM ALL_TAB_COLUMNS WHERE OWNER IN ('模式名','模式名')ORDER BY OWNER,TABLE_NAME,COLUMN_ID;
1

3 查询数据源 A2的表结构

SELECT 'A2' AS SOURCE,OWNER,TABLE_NAME,COLUMN_NAME,DATA_TYPE,DATA_LENGTH,COLUMN_ID FROM ALL_TAB_COLUMNS WHERE OWNER IN ('模式名','模式名') ORDER BY OWNER,TABLE_NAME,COLUMN_ID;
1

4 将数据源A1,A2的结果导入比对表中

INSERT INTO SYSDBA.TAB_DIFF (SELECT 'A1' AS SOURCE,OWNER,TABLE_NAME,COLUMN_NAME,DATA_TYPE,DATA_LENGTH,COLUMN_ID FROM ALL_TAB_COLUMNS WHERE OWNER IN ('模式名','模式名') ORDER BY OWNER,TABLE_NAME,COLUMN_ID);

COMMIT;
1
2
3

INSERT INTO SYSDBA.TAB_DIFF (SELECT 'A2' AS SOURCE,OWNER,TABLE_NAME,COLUMN_NAME,DATA_TYPE,DATA_LENGTH,COLUMN_ID FROM ALL_TAB_COLUMNS WHERE OWNER IN ('模式名','模式名') ORDER BY OWNER,TABLE_NAME,COLUMN_ID);

COMMIT;
1
2
3

5 查询比对结果

SELECT * FROM (SELECT * FROM SYSDBA.TAB_DIFF WHERE SOURCE='A1') A1  FULL OUTER JOIN (SELECT * FROM SYSDBA.TAB_DIFF WHERE SOURCE='A2') A2 ON A1.OWNER=A2.OWNER AND A1.TABLE_NAME=A2.TABLE_NAME AND A1.COLUMN_NAME = A2.COLUMN_NAME;
1

6 结果中 NULL 空的为差异部分