Oracle12.2 SQL言語リファレンス COMMENT 使用されるOracleのバージョンのマニュアルを確認してからお使いください。
comment_on_table.sql
--
-- COMMENT ON TABLE 文, COMMENT ON MATERIALIZED VIEW 文取得
--
-- history
-- 2018/04/20 PKG_USERS_SCHEMA 対応 かなり遅くなったような？
--
SET ECHO OFF
SET FEEDBACK OFF
SET HEADING OFF
SET LINESIZE 500
SET PAGESIZE 10000
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 comment_on_table_&&_CONNECT_IDENTIFIER._&&USER_NAME&&SYSTEM_DATE..sql
-- Windows ↑, UNIX/Linux では ↓ を有効に
-- SPOOL comment_on_table_\&&_CONNECT_IDENTIFIER._\&&USER_NAME\&&SYSTEM_DATE..sql
COLUMN COMMENT_ON_TABLE FORMAT A1000
--
-- COMMENT ON MATERIALIZED VIEW, TABLE
--
SELECT
CASE
WHEN A.COMMENTS IS NULL -- まだコメントがない
OR A.TABLE_NAME LIKE 'MLOG\$_%' -- MATERIALIZED VIEW LOG
OR A.TABLE_NAME LIKE 'RUPD\$_%' THEN -- temporary updatable snapshot log
'-- ' -- コメント行に
END
|| 'COMMENT ON '
|| RPAD(A.TABLE_TYPE, 18) -- ↓桁数は現場対応
|| RPAD(A.OWNER || '.' || A.TABLE_NAME, 45) || ' IS '''
|| A.COMMENTS || ''' ;' AS COMMENT_ON_TABLE
FROM(
SELECT
NVL(OB.OBJECT_TYPE, TM.TABLE_TYPE) AS TABLE_TYPE
, TA.OWNER
, TA.TABLE_NAME
, TA.TEMPORARY
, TO_SINGLE_BYTE(TRIM(NVL(TM.COMMENTS, MM.COMMENTS))) AS COMMENTS
FROM(
SELECT COLUMN_VALUE AS OWNER FROM TABLE(PKG_USERS_SCHEMA.GET_USERS_SCHEMA) -- ユーザーが作成したスキーマのみ
) MS
INNER JOIN DBA_TABLES TA
ON TA.OWNER = MS.OWNER
LEFT OUTER JOIN DBA_OBJECTS OB
ON OB.OWNER = MS.OWNER
AND OB.OWNER = TA.OWNER
AND OB.OBJECT_NAME = TA.TABLE_NAME
LEFT OUTER JOIN DBA_TAB_COMMENTS TM
ON TM.OWNER = MS.OWNER
AND TM.OWNER = TA.OWNER
AND TM.TABLE_NAME = TA.TABLE_NAME
LEFT OUTER JOIN DBA_MVIEW_COMMENTS MM
ON MM.OWNER = MS.OWNER
AND MM.OWNER = TA.OWNER
AND MM.MVIEW_NAME = TA.TABLE_NAME
WHERE
TA.DROPPED = 'NO' -- except trash box, since Oracle10.1.0
AND TA.TABLE_NAME NOT IN
(
'CHAINED_ROWS' -- except Oracle side table
, 'EXCEPTIONS'
, 'PLAN_TABLE'
, 'PLAN_TABLE$'
)
AND OB.OBJECT_TYPE IN('MATERIALIZED VIEW', 'TABLE')
-- AND TM.COMMENTS IS NOT NULL -- コメントが設定されていないテーブルのみ
) A
ORDER BY
A.OWNER
, A.TABLE_NAME
, A.TABLE_TYPE ;
SPOOL OFF
COLUMN COMMENT_ON_TABLE CLEAR
SET ECHO ON
SET FEEDBACK ON
SET HEADING ON
SET LINESIZE 80
SET TERMOUT ON