【Oracle】テーブル・列のコメント（論理名）を取得・設定する方法｜USER/ALL/DBA_TAB_COMMENTS・COMMENT ON・一括確認・未設定検出まで完全解説

SELECT table_name,
       table_type,
       comments
  FROM user_tab_comments
 WHERE table_type = 'TABLE'
 ORDER BY table_name;

-- 特定スキーマのテーブルコメントを一覧表示
SELECT table_name, table_type, comments
  FROM all_tab_comments
 WHERE owner = 'HR'
   AND table_type = 'TABLE'
 ORDER BY table_name;

-- 特定テーブル1件のコメントを取得
SELECT table_name, comments
  FROM all_tab_comments
 WHERE owner      = 'HR'
   AND table_name = 'EMPLOYEES';

-- コメントに「マスタ」を含むテーブルを検索
SELECT table_name, comments
  FROM user_tab_comments
 WHERE comments LIKE '%マスタ%'
 ORDER BY table_name;

SELECT column_name, comments
  FROM all_col_comments
 WHERE owner      = 'HR'
   AND table_name = 'EMPLOYEES'
 ORDER BY column_name;

SELECT ac.column_id,
       ac.column_name,
       cc.comments
  FROM all_tab_columns  ac
  JOIN all_col_comments cc
    ON  cc.owner       = ac.owner
   AND  cc.table_name  = ac.table_name
   AND  cc.column_name = ac.column_name
 WHERE ac.owner      = 'HR'
   AND ac.table_name = 'EMPLOYEES'
 ORDER BY ac.column_id;

SELECT
  tc.table_name,
  tc.comments         AS table_comment,
  ac.column_id,
  ac.column_name,
  cc.comments         AS column_comment
  FROM all_tab_comments  tc
  JOIN all_col_comments  cc
    ON  cc.owner      = tc.owner
   AND  cc.table_name = tc.table_name
  JOIN all_tab_columns  ac
    ON  ac.owner      = cc.owner
   AND  ac.table_name = cc.table_name
   AND  ac.column_name= cc.column_name
 WHERE tc.owner      = 'HR'
   AND tc.table_name = 'EMPLOYEES'
 ORDER BY ac.column_id;

COMMENT ON TABLE employees IS '社員マスタ';

COMMENT ON COLUMN employees.employee_id IS '社員ID';
COMMENT ON COLUMN employees.first_name  IS '名';
COMMENT ON COLUMN employees.last_name   IS '姓';
COMMENT ON COLUMN employees.hire_date   IS '入社日';
COMMENT ON COLUMN employees.salary      IS '月額給与（円）';

-- 空文字列を設定するとコメントが削除される（NULL に戻る）
COMMENT ON TABLE  employees          IS '';
COMMENT ON COLUMN employees.salary   IS '';

-- スキーマ名.テーブル名 で指定
COMMENT ON TABLE  hr.employees          IS '社員マスタ';
COMMENT ON COLUMN hr.employees.salary   IS '月額給与（円）';

-- ビューにコメントを設定
COMMENT ON TABLE v_active_employees IS '在籍社員ビュー';

-- ビューの列にコメントを設定
COMMENT ON COLUMN v_active_employees.full_name IS '氏名（姓＋名）';

-- マテリアライズドビューにコメントを設定
COMMENT ON TABLE mv_sales_summary IS '売上集計マテビュー（日次更新）';

SELECT table_name, table_type
  FROM user_tab_comments
 WHERE (comments IS NULL OR comments = '')
   AND table_type = 'TABLE'
 ORDER BY table_name;

-- 特定テーブルでコメント未設定の列を抽出
SELECT column_name
  FROM user_col_comments
 WHERE table_name = 'EMPLOYEES'
   AND (comments IS NULL OR comments = '')
 ORDER BY column_name;

-- テーブルごとにコメント未設定列の件数を集計
SELECT cc.table_name,
       COUNT(*) AS no_comment_cols,
       (SELECT COUNT(*) FROM user_tab_columns tc
         WHERE tc.table_name = cc.table_name) AS total_cols
  FROM user_col_comments cc
 WHERE (cc.comments IS NULL OR cc.comments = '')
 GROUP BY cc.table_name
 ORDER BY no_comment_cols DESC;

SELECT DBMS_METADATA.GET_DEPENDENT_DDL(
         'COMMENT',
         'EMPLOYEES',
         'HR'
       ) AS comment_ddl
  FROM dual;

BEGIN
  FOR rec IN (
    SELECT table_name, column_name
      FROM user_col_comments
     WHERE comments IS NULL
  ) LOOP
    EXECUTE IMMEDIATE
      'COMMENT ON COLUMN ' || rec.table_name
      || '.' || rec.column_name
      || ' IS ''【要設定】''';
  END LOOP;
END;
/

-- テーブルコメントの設定例
BEGIN
  EXECUTE IMMEDIATE
    q'[COMMENT ON TABLE employees IS '社員マスタ']';
  EXECUTE IMMEDIATE
    q'[COMMENT ON COLUMN employees.employee_id IS '社員ID']';
  EXECUTE IMMEDIATE
    q'[COMMENT ON COLUMN employees.first_name IS '名']';
  EXECUTE IMMEDIATE
    q'[COMMENT ON COLUMN employees.last_name IS '姓']';
  EXECUTE IMMEDIATE
    q'[COMMENT ON COLUMN employees.hire_date IS '入社日']';
  DBMS_OUTPUT.PUT_LINE('コメント設定完了');
END;
/

SELECT
  ac.column_id         AS "No.",
  ac.column_name       AS "物理名",
  cc.comments          AS "論理名",
  ac.data_type
    || CASE
         WHEN ac.data_precision IS NOT NULL
         THEN '(' || ac.data_precision || ',' || ac.data_scale || ')'
         WHEN ac.char_length > 0
         THEN '(' || ac.char_length || ')'
         ELSE ''
       END             AS "型・桁",
  ac.nullable          AS "NULL可否",
  ac.data_default      AS "デフォルト値"
  FROM all_tab_columns  ac
  JOIN all_col_comments cc
    ON  cc.owner       = ac.owner
   AND  cc.table_name  = ac.table_name
   AND  cc.column_name = ac.column_name
 WHERE ac.owner      = 'HR'
   AND ac.table_name = 'EMPLOYEES'
 ORDER BY ac.column_id;