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

祢真伟大

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

阅读量2.7k

点赞数 4

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

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

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

版权

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

91 篇文章

订阅专栏

DMSQL

39 篇文章

订阅专栏

该博客介绍了如何使用SQL语句在DM8数据库中进行两个数据源之间的表结构对比。通过创建比对表，查询并导入不同数据源的表结构信息，然后进行全外连接查询以找出结构差异，NULL值表示存在的差异部分。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

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;