Oracle12.2 SQL言語リファレンス COMMENT 使用されるOracleのバージョンのマニュアルを確認してからお使いください。
comment_on_column.sql
--
-- COMMENT ON COLUMN 文取得
--
-- history
-- 2018/03/08 PKG_USERS_SCHEMA 対応 かなり遅くなったような？
--
SET ECHO OFF
SET FEEDBACK OFF
SET HEADING OFF
SET LINESIZE 500
SET PAGESIZE 50000
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_column_&&_CONNECT_IDENTIFIER._&&USER_NAME&&SYSTEM_DATE..sql
-- Windows ↑, UNIX/Linux では ↓ を有効に
-- SPOOL comment_on_column_\&&_CONNECT_IDENTIFIER._\&&USER_NAME\&&SYSTEM_DATE..sql
COLUMN COMMNET_ON_COLUMN FORMAT A500
SELECT -- ↓ 桁数は現場対応
'COMMENT ON COLUMN ' || RPAD(TC.OWNER || '.' || TC.TABLE_NAME || '.' || TC.COLUMN_NAME, 81)
|| ' IS ''' || TO_SINGLE_BYTE(TRIM(CM.COMMENTS)) || ''' ; ' AS COMMNET_ON_COLUMN
FROM(
SELECT
TA.OWNER
, TA.TABLE_NAME
FROM(
SELECT COLUMN_VALUE AS OWNER FROM TABLE(PKG_USERS_SCHEMA.GET_USERS_SCHEMA) -- ユーザーが作成したスキーマのみ
) MS
INNER JOIN DBA_TABLES
ON TA.OWNER = MS.OWNER
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$'
)
) A
INNER JOIN DBA_TAB_COLUMNS
ON TC.OWNER = A.OWNER
AND TC.TABLE_NAME = A.TABLE_NAME
LEFT OUTER JOIN DBA_COL_COMMENTS CM
ON CM.OWNER = A.OWNER
AND CM.TABLE_NAME = A.TABLE_NAME
and CM.OWNER = TC.OWNER
AND CM.TABLE_NAME = TC.TABLE_NAME
AND CM.COLUMN_NAME = TC.COLUMN_NAME
-- WHERE
-- TRIM(CM.COMMENTS) IS NOT NULL -- とりあえず、すでにCOMMENTがある列は除く
ORDER BY
TC.COLUMN_NAME
, TC.OWNER
, TC.TABLE_NAME
-- TC.OWNER -- 表示順を変更したい時
--, TC.TABLE_NAME
--, TC.COLUMN_ID
;
SPOOL OFF
COLUMN COMMNET_ON_COLUMN CLEAR
SET ECHO ON
SET FEEDBACK ON
SET HEADING ON
SET LINESIZE 80
SET TERMOUT ON