【Oracle】指定したカラム名が存在するか確認する方法｜ALL_TAB_COLUMNS・型・NULL・複数条件検索まで完全解説

ビュー名	参照できる範囲	OWNER列	必要権限
USER_TAB_COLUMNS	自分が所有するテーブル・ビューの列	なし	なし
ALL_TAB_COLUMNS	アクセス権のある全スキーマの列	あり	なし
DBA_TAB_COLUMNS	DB内の全スキーマの列	あり	DBA権限

大文字・小文字の注意: Oracleのデータディクショナリはカラム名を大文字で格納しています。WHERE COLUMN_NAME = 'email' では一致しません。'EMAIL' と大文字で指定するか、UPPER(COLUMN_NAME) = UPPER('email') と書いてください。

-- アクセス可能な全スキーマから検索
SELECT owner, table_name, column_name
  FROM all_tab_columns
 WHERE column_name = 'EMAIL'
 ORDER BY owner, table_name;

-- 自スキーマのみ検索（シンプル版）
SELECT table_name, column_name
  FROM user_tab_columns
 WHERE column_name = 'EMAIL'
 ORDER BY table_name;

-- "ADDR" を含むカラムを検索
SELECT owner, table_name, column_name
  FROM all_tab_columns
 WHERE column_name LIKE '%ADDR%'
 ORDER BY owner, table_name;

-- "_ID" で終わるカラムをすべて取得
SELECT owner, table_name, column_name
  FROM all_tab_columns
 WHERE column_name LIKE '%\_ID' ESCAPE '\'  -- _ はワイルドカードなのでエスケープ
 ORDER BY owner, table_name;

-- 特定スキーマ内のみで検索
SELECT table_name, column_name
  FROM all_tab_columns
 WHERE owner       = 'HR'
   AND  column_name = 'EMAIL';

-- 特定テーブルにカラムが存在するか確認
SELECT column_name
  FROM all_tab_columns
 WHERE owner       = 'HR'
   AND  table_name  = 'EMPLOYEES'
   AND  column_name = 'EMAIL';

SELECT
  owner,
  table_name,
  column_name,
  column_id,
  data_type
    || CASE
         WHEN data_precision IS NOT NULL
           THEN '(' || data_precision || ',' || data_scale || ')'
         WHEN char_length > 0
           THEN '(' || char_length || ')'
         ELSE ''
       END               AS data_type_full,
  nullable,
  data_default
  FROM all_tab_columns
 WHERE column_name LIKE '%EMAIL%'
 ORDER BY owner, table_name, column_id;

-- HRスキーマ内のCLOB型カラムをすべて検索
SELECT table_name, column_name, data_type
  FROM all_tab_columns
 WHERE owner     = 'HR'
   AND  data_type = 'CLOB'
 ORDER BY table_name;

-- DATE型またはTIMESTAMP型のカラムを検索
SELECT owner, table_name, column_name, data_type
  FROM all_tab_columns
 WHERE data_type IN ('DATE', 'TIMESTAMP')
   AND  owner NOT IN ('SYS', 'SYSTEM')
 ORDER BY owner, table_name;

-- EMPLOYEES テーブルに EMAIL カラムが存在するか
SELECT
  CASE
    WHEN EXISTS (
      SELECT 1 FROM all_tab_columns
       WHERE owner       = 'HR'
         AND  table_name  = 'EMPLOYEES'
         AND  column_name = 'EMAIL'
    ) THEN '存在する'
    ELSE '存在しない'
  END AS column_exists
  FROM dual;

-- 各カラム名が何テーブルに存在するかカウント（上位20件）
SELECT column_name,
       COUNT(*) AS table_count
  FROM all_tab_columns
 WHERE owner NOT IN ('SYS', 'SYSTEM', 'MDSYS')
 GROUP BY column_name
 ORDER BY table_count DESC
 FETCH FIRST 20 ROWS ONLY;

目的	クエリのポイント
完全一致検索	WHERE column_name = ‘EMAIL’（大文字必須）
部分一致検索	WHERE column_name LIKE ‘%ADDR%’
詳細情報も一緒に取得	data_type, char_length, nullable, data_default
存在有無だけ確認	CASE WHEN EXISTS (…) THEN ‘存在する’ END
自スキーマのみ調べる	USER_TAB_COLUMNS（OWNER列なし、高速）