参考逻辑导出工具dexp说明

搞个过程

CREATE OR REPLACE PROCEDURE SP_SHOW_TABLE_DEFINE ( SCH_NAME VARCHAR2 ( 500),
                                                   TAB_NAME VARCHAR2 ( 1000) )
as
    V_INX_ID VARCHAR2(200);
    /*索引编号传参*/
    V_SQL VARCHAR2(1000);
    /*索引定义*/
    V_SQL_ALL VARCHAR2(1000);
    /*打印的结果*/
    v_sql1 varchar2(8000);
    /*表结构+主键+外键+约束*/
    v_sql2 varchar2(8000);
    /*表及字段注释*/
    V_CUR
    CURSOR;
    BEGIN
        v_sql1 = TABLEDEF(SCH_NAME, TAB_NAME);
        /*表结构+主键+外键+约束*/
        /*表及字段注释*/
        select (SELECT LISTAGG(COMMENTS_1, char(13))
                  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 <> '索引不存在'
                       and INDEX_1 <> '禁止查看系统定义的索引信息' ))
              INTO V_SQL
              FROM DUAL ;
            
            /*索引定义*/
            /*打印结果*/
            V_SQL_ALL = V_SQL_ALL || '' || char(13) || V_SQL;
            /*打印结果V_SQL*/
        END LOOP;
        /*打印结果*/
        V_SQL_ALL = v_sql1 || '' || V_SQL_ALL || '' || char(13) || v_sql2;
        /*或者查询结果v_sql1+v_sql2*/
        select V_SQL_ALL;
    
    END;
/
call  "SP_SHOW_TABLE_DEFINE"('DM_SCHEMA', 'TABLE_1');