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

祢真伟大

已于 2023-04-07 14:30:43 修改

阅读量1.6k

点赞数 3

分类专栏： DMSQL 数据库文章标签：数据库 sql

于 2022-09-16 14:58:24 首次发布

本文链接：https://blog.csdn.net/qq_37358909/article/details/126890519

版权

数据库同时被 2 个专栏收录

86 篇文章 16 订阅

订阅专栏

DMSQL

31 篇文章 2 订阅

订阅专栏

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

1 创建比对表 TAB_DIFF
2 查询数据源 A1的表结构
3 查询数据源 A2的表结构
4 将数据源A1,A2的结果导入比对表中
5 查询比对结果
6 结果中 NULL 空的为差异部分

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);

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;

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;

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;

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;

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;