ドメイン・チェッカー をちょっと改造して簡易テーブル定義書を取得

list_tables.sql
--
-- 簡易テーブル定義書
--
-- テーブル定義書などドキュメントをきちんと管理できいるユーザーは少ない。
-- 酷いところになるとテーブル定義書すらない。
--
-- ならば、データ・ディクショナリ・ビューから作ってしまおう。あらかじめ、COMMENT ON 文は実行しておこうね。
--
--  history
--    2018/03/08 USER_MVIEW_COMMENTS を追加
--
SET ECHO OFF
SET FEEDBACK OFF
SET HEADING ON
SET LINESIZE 300
SET PAGESIZE 50000
SET TAB OFF
SET TERMOUT OFF
SET TRIMSPOOL ON
COLUMN TABLE_NAME      FORMAT A30
COLUMN TABLE_COMMENT   FORMAT A100
COLUMN COLUMN_NAME     FORMAT A30
COLUMN DATA_TYPE       FORMAT A20
COLUMN NULLABLE        FORMAT A8
COLUMN DATA_DEFAULT    FORMAT A22
COLUMN COLUMN_COMMENT  FORMAT A100
BREAK ON TABLE_NAME SKIP 1 ON TABLE_COMMENT
-- 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
SELECT
    TC.TABLE_NAME
  , '  -- ' || TRIM(TO_SINGLE_BYTE(NVL(TM.COMMENTS, MM.COMMENTS))) AS TABLE_COMMENTS
  , 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  -- 改行が入ってしまうこともある。
  , NVL2(CM.COMMENTS, '-- ' || TRIM(TO_SINGLE_BYTE(CM.COMMENTS)), NULL) AS COLUMN_COMMENT
FROM USER_TABLES TA
LEFT OUTER JOIN USER_TAB_COMMENTS TM
ON  TM.TABLE_NAME = TA.TABLE_NAME
LEFT OUTER JOIN USER_MVIEW_COMMENTS MM
ON  MM.MVIEW_NAME = TA.TABLE_NAME
INNER JOIN USER_TAB_COLUMNS TC
ON  TC.TABLE_NAME = TA.TABLE_NAME
LEFT OUTER JOIN USER_COL_COMMENTS CM
ON  CM.TABLE_NAME  = TC.TABLE_NAME
AND CM.COLUMN_NAME = TC.COLUMN_NAME
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$'
    )
ORDER BY
    TC.TABLE_NAME
  , TC.COLUMN_ID ;
SPOOL OFF
CLEAR BREAKS
COLUMN TABLE_NAME      CLEAR
COLUMN TABLE_COMMENT   CLEAR
COLUMN COLUMN_NAME     CLEAR
COLUMN DATA_TYPE       CLEAR
COLUMN NULLABLE        CLEAR
COLUMN DATA_DEFAULT    CLEAR
COLUMN COLUMN_COMMENT  CLEAR
SET ECHO OFF
SET FEEDBACK ON
SET LINESIZE 80

参考
COMMENT文は以下のリンクを参考に。
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.