达梦获取表及相关定义

达梦获取表及相关定义

获取表相关定义，除表结构外的对象，例如索引、注释等
达梦在线服务平台

图形化

--创建外键需先创建依赖表
CREATE TABLE "SYSDBA"."TABLE_2"
("C1" VARCHAR(100) NOT NULL,
"C2" VARCHAR(100),
NOT CLUSTER PRIMARY KEY("C1"));


--"SYSDBA"."TABLE_1"表定义
CREATE TABLE "SYSDBA"."TABLE_1"
(
"C1" VARCHAR(100) NOT NULL,
"C2" VARCHAR(100),
"C3" VARCHAR(100),
NOT CLUSTER PRIMARY KEY("C1"),
CONSTRAINT "CESHI" FOREIGN KEY("C1") REFERENCES "SYSDBA"."TABLE_2"("C1"))  ;
COMMENT ON TABLE "SYSDBA"."TABLE_1" IS '测试表';
COMMENT ON COLUMN "SYSDBA"."TABLE_1"."C1" IS '字段1';
COMMENT ON COLUMN "SYSDBA"."TABLE_1"."C2" IS '字段2';
CREATE  INDEX "11" ON "SYSDBA"."TABLE_1"("C1" ASC) ;
CREATE  INDEX "33" ON "SYSDBA"."TABLE_1"("C2" ASC)  ;
CREATE  INDEX "3232" ON "SYSDBA"."TABLE_1"("C2" ASC,"C3" ASC)  ;

存储过程查询

无图形化环境时可使用存储过程实现
测试版本

CREATE OR REPLACE PROCEDURE SP_SHOW_TABLE_DEFINE ( SCH_NAME VARCHAR2 ( 500),
                                                   TAB_NAME VARCHAR2 ( 1000) )
as
    V_INX_ID VARCHAR2(8000);
    /*索引编号传参*/
    V_SQL VARCHAR2(8000);
    /*索引定义*/
    V_SQL_ALL VARCHAR2(8000);
    /*打印的结果*/
    v_sql1 varchar2(8000);
    /*表结构+主键+外键+约束*/
    v_sql2 varchar2(8000);
    /*表及字段注释*/
    V_CUR
    CURSOR;
    BEGIN
        v_sql1 = TABLEDEF(SCH_NAME,TAB_NAME);
        /*表结构+主键+外键+约束*/
        /*表及字段注释*/
        select (SELECT LISTAGG(COMMENTS_1)
                  from ( SELECT 'COMMENT ON TABLE '||T.SCHNAME||'.'||T.TVNAME||' IS '''||REPLACE(T.COMMENT$,'''','''''')||''';' AS COMMENTS_1
                          FROM SYSTABLECOMMENTS T
                         WHERE SCHNAME=SCH_NAME
                           AND TVNAME=TAB_NAME
                       UNION
                       SELECT 'COMMENT ON COLUMN '||C.SCHNAME||'.'||C.TVNAME||'.'||C.COLNAME||' IS '''||REPLACE(C.COMMENT$,'''','''''')||''';'
                         FROM SYSCOLUMNCOMMENTS C
                        WHERE SCHNAME=SCH_NAME
                          AND TVNAME=TAB_NAME ))
          INTO
               v_sql2
          FROM DUAL;
        
        /*表及字段注释*/
        /*获取索引编号传参*/
        OPEN V_CUR FOR
        SELECT T2.OBJECT_ID
          FROM ALL_INDEXES T1,
               ALL_OBJECTS T2
         WHERE T1.OWNER =T2.OWNER
           AND T1.INDEX_NAME=T2.OBJECT_NAME
           AND T2.GENERATED ='N'
           AND T1.OWNER =SCH_NAME
           AND T1.TABLE_NAME=TAB_NAME;
        
        /*获取索引编号传参*/
        LOOP
            /*索引编号传参*/
            FETCH V_CUR INTO V_INX_ID;
            
            EXIT WHEN V_CUR%NOTFOUND;
            --PRINT V_INX_ID;
            --DBMS_OUTPUT.PUT_LINE(V_INX_ID);
            /*索引定义*/
            SELECT ((SELECT *
                      FROM (SELECT INDEXDEF(V_INX_ID,1) AS INDEX_1)
                     WHERE INDEX_1 <> '索引不存在'))
              INTO
                   V_SQL
              FROM DUAL ;
            
            /*索引定义*/
            /*打印结果*/
            V_SQL_ALL=V_SQL_ALL||''||V_SQL;
            /*打印结果V_SQL*/
        END LOOP;
        /*打印结果*/
        V_SQL_ALL=v_sql1||''||V_SQL_ALL||''||v_sql2;
        /*打印结果v_sql1+v_sql2*/
        print V_SQL_ALL;
    END;
/
 

执行测试，复制结果比对一致

call "SYSDBA"."SP_SHOW_TABLE_DEFINE"('SYSDBA','TABLE_1');

CREATE TABLE "SYSDBA"."TABLE_1"
(
"C1" VARCHAR(100) NOT NULL,
"C2" VARCHAR(100),
"C3" VARCHAR(100),
NOT CLUSTER PRIMARY KEY("C1"),
CONSTRAINT "CESHI" FOREIGN KEY("C1") REFERENCES "SYSDBA"."TABLE_2"("C1")) STORAGE(ON "MAIN", CLUSTERBTR) ;
CREATE  INDEX "11" ON "SYSDBA"."TABLE_1"("C1" ASC) STORAGE(ON "MAIN", CLUSTERBTR) ;
CREATE  INDEX "3232" ON "SYSDBA"."TABLE_1"("C2" ASC,"C3" ASC) STORAGE(ON "MAIN", CLUSTERBTR) ;
CREATE  INDEX "33" ON "SYSDBA"."TABLE_1"("C2" ASC) STORAGE(ON "MAIN", CLUSTERBTR) ;
COMMENT ON TABLE SYSDBA.TABLE_1 IS '测试表';
COMMENT ON COLUMN SYSDBA.TABLE_1.C1 IS '字段1';
COMMENT ON COLUMN SYSDBA.TABLE_1.C2 IS '字段2';