Oracle 簡易テーブル定義書 SQL版 をちょっと改造してPL/SQLに。ちょっと見易くなったかも?

--
-- 簡易テーブル定義書 PL/SQL
--
-- テーブル定義書などドキュメントをきちんと管理できいるユーザーは少ない。
-- 酷いところになるとテーブル定義書すらない。
--
-- ならば、データ・ディクショナリ・ビューから作ってしまおう。あらかじめ、COMMENT ON 文は実行しておこうね。
--
SET ECHO OFF
SET FEEDBACK OFF
SET HEADING OFF
SET LINESIZE 600
SET PAGESIZE 50000
SET SERVEROUTPUT ON SIZE UNLIMITED FORMAT WRAPPED
SET TAB OFF
SET TERMOUT OFF
SET TRIMSPOOL ON
-- SPOOL pass includes CONNECT_IDENTIFIER, USER_NAME and SYSDATE
COLUMN USER_NAME    NEW_VALUE USER_NAME
COLUMN SYSTEM_DATE  NEW_VALUE SYSTEM_DATE
SELECT USER || '_' AS USER_NAME
     , TO_CHAR(SYSDATE, 'YYYYMMDD_HH24MISS') AS SYSTEM_DATE
FROM DUAL ;
SPOOL list_tables_&&_CONNECT_IDENTIFIER._&&USER_NAME&&SYSTEM_DATE..log
-- Windows ↑,  UNIX/Linux では ↓ を有効に
-- SPOOL list_tables_\&&_CONNECT_IDENTIFIER._\&&USER_NAME\&&SYSTEM_DATE..log
SET TIMING ON
DECLARE
    MY_NAME  VARCHAR2(11) := 'LIST_TABLES' ;
    L_SQLERRM  VARCHAR2(2048) ;

    -- LIST TABLE
    CURSOR CUR_TA
    IS
        SELECT
            TC.TABLE_NAME
          , '  -- ' || TRIM(TO_SINGLE_BYTE(NVL(TM.COMMENTS, MM.COMMENTS))) AS TABLE_COMMENTS
          , TC.COLUMN_ID
          , TC.COLUMN_NAME
          , TC.DATA_TYPE
         || CASE  -- 対応していないデータ型もあるはず。
                WHEN TC.DATA_TYPE IN('BLOB', 'CLOB', 'DATE', 'LONG') THEN
                    NULL
                WHEN TC.DATA_TYPE LIKE 'TIMESTAMP%' THEN
                    NULL
                WHEN TC.DATA_TYPE = 'NUMBER' THEN  -- この辺はかなり雑、
                    NVL2(TC.DATA_PRECISION  -- NUMBER型の時の全体の桁数
                       , '(' || TO_CHAR(TC.DATA_PRECISION) ||
                         CASE
                             WHEN TC.DATA_SCALE <> 0  -- NUMBER型の時の小数点
                             AND  TC.DATA_SCALE IS NOT NULL THEN
                                  '.' || LTRIM(TO_CHAR(TC.DATA_SCALE))
                         END || ')', NULL)
                ELSE
                    '(' || TO_CHAR(TC.DATA_LENGTH) || ')'
            END AS DATA_TYPE
          , DECODE(TC.NULLABLE, 'N', 'NOT NULL') AS NULLABLE
          , TC.DATA_DEFAULT  -- LONG型:改行が入ってしまうこともある。
          , NVL2(CM.COMMENTS, '-- ' || TRIM(TO_SINGLE_BYTE(CM.COMMENTS)), NULL) AS COLUMN_COMMENTS
        FROM(
            SELECT
                TA.TABLE_NAME
            FROM USER_TABLES TA
            WHERE
                TA.DROPPED = 'NO'  -- except trash box, since Oracle10.1.0
            AND TA.IOT_TYPE IS NULL  -- except index composition table
            AND TA.TABLE_NAME NOT IN  -- except Oracle side table
                (
                'CHAINED_ROWS'
              , 'EXCEPTIONS'
              , 'PLAN_TABLE'
              , 'PLAN_TABLE$'
                )
            ) A
        LEFT OUTER JOIN USER_TAB_COMMENTS TM
        ON  TM.TABLE_NAME = A.TABLE_NAME
        LEFT OUTER JOIN USER_MVIEW_COMMENTS MM
        ON  MM.MVIEW_NAME = A.TABLE_NAME
        INNER JOIN USER_TAB_COLUMNS TC
        ON  TC.TABLE_NAME = A.TABLE_NAME
        LEFT OUTER JOIN USER_COL_COMMENTS CM
        ON  CM.TABLE_NAME = A.TABLE_NAME
        AND CM.TABLE_NAME  = TC.TABLE_NAME
        AND CM.COLUMN_NAME = TC.COLUMN_NAME
        ORDER BY
            TC.TABLE_NAME
          , TC.COLUMN_ID ;

    REC_TA  CUR_TA%ROWTYPE ;
    SAVED_TABLE_NAME  USER_TABLES.TABLE_NAME%TYPE ;
BEGIN
    OPEN CUR_TA ;
    FETCH CUR_TA INTO REC_TA ;
    LOOP
        EXIT WHEN CUR_TA%NOTFOUND ;
        SAVED_TABLE_NAME := REC_TA.TABLE_NAME ;
        DBMS_OUTPUT.PUT_LINE('TABLE ' || REC_TA.TABLE_NAME || REC_TA.TABLE_COMMENTS) ;
        DBMS_OUTPUT.PUT_LINE('     NO COLUMN_NAME                    DATA_TYPE                   NULL ?   DEFAULT                          COMMENTS') ;
        DBMS_OUTPUT.PUT_LINE('   ---- ------------------------------ --------------------------- -------- -------------------------------- ---------------------------------------------') ;
        LOOP
            EXIT WHEN CUR_TA%NOTFOUND
            OR   REC_TA.TABLE_NAME <> SAVED_TABLE_NAME ;
            DBMS_OUTPUT.PUT_LINE('  '
                              || TO_CHAR(REC_TA.COLUMN_ID, '9990') || ' '
                              || RPAD(REC_TA.COLUMN_NAME , 31)
                              || RPAD(REC_TA.DATA_TYPE   , 28)
                              || RPAD(REC_TA.NULLABLE    ,  9)
                              || RPAD(NVL(REC_TA.DATA_DEFAULT, ' '), 32)
                              || REC_TA.COLUMN_COMMENTS) ;
            FETCH CUR_TA INTO REC_TA ;
        END LOOP ;
        DBMS_OUTPUT.NEW_LINE ;
    END LOOP ;
    CLOSE CUR_TA ;
EXCEPTION
    WHEN OTHERS THEN
        L_SQLERRM := SQLERRM ;
        IF CUR_TA%ISOPEN THEN
            CLOSE CUR_TA ;
        END IF ;
        DBMS_OUTPUT.PUT_LINE(MY_NAME || ' ERROR:' || L_SQLERRM) ;
        RAISE ;
END ;
/
SPOOL OFF
SET TIMING OFF
SET ECHO OFF
SET FEEDBACK ON
SET HEADING ON
SET LINESIZE 80

-- 参考
Oracle COMMENT ON TABLE 文, COMMENT ON MATERIALIZED VIEW 文取得
Oracle COMMENT ON COLUMN 文取得

Sign up for free and join this conversation.
Sign Up
If you already have a Qiita account log in.