Oracle 查詢使用者所有表、欄位與註解的實用 SQL 集合

前言

開發、文檔產生、影響分析常需取得：某 Schema 擁有哪些表、每張表有哪些欄位、欄位註解為何。本文彙整 USER_*、ALL_* 視圖的高頻查詢組合，可直接套用。

1. 表/視圖清單與註解

-- 當前使用者
SELECT table_name, table_type, comments
FROM   user_tab_comments
WHERE  table_type IN ('TABLE','VIEW')
ORDER BY table_name;

-- 所有可存取使用者 (需權限)
SELECT owner, table_name, table_type, comments
FROM   all_tab_comments
ORDER BY owner, table_name;

2. 欄位定義與註解

-- 當前使用者：欄位資料型別、長度、精度、可空、預設值、註解
SELECT c.table_name, c.column_name, c.data_type, c.data_length,
       c.data_precision, c.data_scale, c.nullable, c.data_default,
       cc.comments
FROM   user_tab_columns c
LEFT JOIN user_col_comments cc
  ON   c.table_name = cc.table_name
 AND   c.column_name = cc.column_name
ORDER BY c.table_name, c.column_id;

-- 所有可存取使用者
SELECT c.owner, c.table_name, c.column_name, c.data_type, c.data_length,
       c.data_precision, c.data_scale, c.nullable, c.data_default,
       cc.comments
FROM   all_tab_columns c
LEFT JOIN all_col_comments cc
  ON   c.owner = cc.owner
  AND  c.table_name = cc.table_name
  AND  c.column_name = cc.column_name
ORDER BY c.owner, c.table_name, c.column_id;

3. 常用組合查詢：表 + 欄位 + 註解一次取得

SELECT t.table_name, t.comments AS table_comment,
       c.column_name, c.data_type || 
         CASE WHEN c.data_precision IS NOT NULL 
              THEN '('||c.data_precision||','||c.data_scale||')'
              WHEN c.data_length IS NOT NULL 
              THEN '('||c.data_length||')' END AS data_type_full,
       c.nullable, c.data_default, cc.comments AS column_comment
FROM   user_tab_comments t
JOIN   user_tab_columns c ON t.table_name = c.table_name
LEFT JOIN user_col_comments cc 
  ON   c.table_name = cc.table_name
 AND   c.column_name = cc.column_name
WHERE  t.table_type = 'TABLE'
ORDER BY t.table_name, c.column_id;

4. 進階：產生 Markdown/HTML 文檔片段

SELECT '## ' || table_name || ' - ' || NVL(table_comment, '無說明') AS md_header
FROM   user_tab_comments
WHERE  table_type = 'TABLE'
UNION ALL
SELECT '| ' || column_name || ' | ' || data_type_full || ' | ' || 
       DECODE(nullable,'Y','可空','N','非空') || ' | ' || 
       NVL(data_default,'') || ' | ' || NVL(column_comment,'') || ' |'
FROM (
  SELECT c.table_name, c.column_name, c.data_type, c.data_length,
         c.data_precision, c.data_scale, c.nullable, c.data_default,
         cc.comments AS column_comment,
         c.data_type || 
           CASE WHEN c.data_precision IS NOT NULL 
                THEN '('||c.data_precision||','||c.data_scale||')'
                WHEN c.data_length IS NOT NULL 
                THEN '('||c.data_length||')' END AS data_type_full
  FROM   user_tab_columns c
  LEFT JOIN user_col_comments cc
    ON   c.table_name = cc.table_name
   AND   c.column_name = cc.column_name
)
ORDER BY table_name, column_id;

視圖選擇指南