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

-- EMPLOYEE_ID というカラムがどのテーブルにあるか
SELECT owner, table_name, column_name, data_type, nullable
FROM all_tab_columns
WHERE column_name = 'EMPLOYEE_ID'   -- 大文字で指定
ORDER BY owner, table_name;

-- 「EMAIL」を含むカラムを検索
SELECT owner, table_name, column_name, data_type
FROM all_tab_columns
WHERE column_name LIKE '%EMAIL%'
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;

-- DATE / TIME / TIMESTAMP を含むカラムを一括検索
SELECT owner, table_name, column_name, data_type
FROM all_tab_columns
WHERE REGEXP_LIKE(column_name, '(DATE|TIME|TIMESTAMP)')
ORDER BY owner, table_name;

-- 連番サフィックス付きカラム（ADDR1, ADDR2, ADDR3 等）
SELECT owner, table_name, column_name
FROM all_tab_columns
WHERE REGEXP_LIKE(column_name, '^ADDR[0-9]+$')
ORDER BY owner, table_name, column_name;

-- EMPLOYEES テーブルの全カラム情報
SELECT column_name, data_type,
       CASE
           WHEN data_type = 'NUMBER' AND data_precision IS NOT NULL
               THEN data_type || '(' || data_precision || ',' || NVL(data_scale, 0) || ')'
           WHEN data_type IN ('VARCHAR2', 'CHAR', 'NVARCHAR2')
               THEN data_type || '(' || char_length || CASE char_used WHEN 'C' THEN ' CHAR' ELSE '' END || ')'
           ELSE data_type
       END AS full_type,
       nullable,
       data_default,
       column_id
FROM all_tab_columns
WHERE owner = 'HR' AND table_name = 'EMPLOYEES'
ORDER BY column_id;

-- SQL*Plus の DESC と同等の情報
SELECT column_name AS "Name",
       CASE nullable WHEN 'Y' THEN NULL ELSE 'NOT NULL' END AS "Null?",
       CASE
           WHEN data_type LIKE '%CHAR%'
               THEN data_type || '(' || char_length || ')'
           WHEN data_type = 'NUMBER' AND data_precision IS NOT NULL
               THEN 'NUMBER(' || data_precision || ',' || data_scale || ')'
           ELSE data_type
       END AS "Type"
FROM all_tab_columns
WHERE owner = 'HR' AND table_name = 'EMPLOYEES'
ORDER BY column_id;

-- DATE 型の全カラム（日付関連のカラムを一括検出）
SELECT owner, table_name, column_name
FROM all_tab_columns
WHERE data_type = 'DATE'
  AND owner NOT IN ('SYS', 'SYSTEM', 'XDB')
ORDER BY owner, table_name;

-- CLOB / BLOB を含むテーブル（LOB 管理の調査）
SELECT owner, table_name, column_name, data_type
FROM all_tab_columns
WHERE data_type IN ('CLOB', 'BLOB', 'NCLOB')
  AND owner NOT IN ('SYS', 'SYSTEM')
ORDER BY owner, table_name;

-- VARCHAR2(4000) の列（長すぎるかもしれない列の調査）
SELECT owner, table_name, column_name, char_length
FROM all_tab_columns
WHERE data_type = 'VARCHAR2' AND char_length >= 4000
  AND owner NOT IN ('SYS', 'SYSTEM')
ORDER BY char_length DESC;

-- 特定テーブルの NOT NULL カラム
SELECT column_name, data_type, nullable, data_default
FROM all_tab_columns
WHERE owner = 'HR' AND table_name = 'EMPLOYEES'
  AND nullable = 'N'
ORDER BY column_id;

-- デフォルト値があるカラムを検索
SELECT owner, table_name, column_name, data_default
FROM all_tab_columns
WHERE data_default IS NOT NULL
  AND owner = 'HR'
ORDER BY table_name, column_id;

-- DEPARTMENT_ID が含まれる全テーブル
SELECT owner, table_name, column_name, data_type, nullable
FROM all_tab_columns
WHERE column_name = 'DEPARTMENT_ID'
  AND owner NOT IN ('SYS', 'SYSTEM')
ORDER BY owner, table_name;

-- テーブル数も集計
SELECT column_name, COUNT(*) AS table_count
FROM all_tab_columns
WHERE column_name = 'DEPARTMENT_ID'
  AND owner NOT IN ('SYS', 'SYSTEM')
GROUP BY column_name;

-- 主キー制約のカラムで _ID で終わらないもの（命名規則違反の検出）
SELECT c.owner, c.table_name, cc.column_name
FROM all_constraints c
JOIN all_cons_columns cc ON c.constraint_name = cc.constraint_name AND c.owner = cc.owner
WHERE c.constraint_type = 'P'
  AND cc.column_name NOT LIKE '%\_ID' ESCAPE '\'
  AND c.owner NOT IN ('SYS', 'SYSTEM')
ORDER BY c.owner, c.table_name;

-- テーブル A にあってテーブル B にないカラム
SELECT column_name, data_type, nullable
FROM all_tab_columns
WHERE owner = 'HR' AND table_name = 'EMPLOYEES'
MINUS
SELECT column_name, data_type, nullable
FROM all_tab_columns
WHERE owner = 'HR' AND table_name = 'EMPLOYEES_BACKUP';

-- 逆: B にあって A にないカラム
SELECT column_name, data_type, nullable
FROM all_tab_columns
WHERE owner = 'HR' AND table_name = 'EMPLOYEES_BACKUP'
MINUS
SELECT column_name, data_type, nullable
FROM all_tab_columns
WHERE owner = 'HR' AND table_name = 'EMPLOYEES';

-- 同じカラム名でデータ型が異なるテーブルの組み合わせ
SELECT a.table_name AS table_a, b.table_name AS table_b,
       a.column_name,
       a.data_type AS type_a, b.data_type AS type_b
FROM all_tab_columns a
JOIN all_tab_columns b ON a.column_name = b.column_name
                      AND a.owner = b.owner
                      AND a.table_name < b.table_name
WHERE a.data_type <> b.data_type
  AND a.owner = 'HR'
ORDER BY a.column_name, a.table_name;

DECLARE
    v_count NUMBER;
BEGIN
    SELECT COUNT(*) INTO v_count
    FROM user_tab_columns
    WHERE table_name = 'EMPLOYEES'
      AND column_name = 'MIDDLE_NAME';

    IF v_count = 0 THEN
        -- カラムが存在しない → 追加
        EXECUTE IMMEDIATE 'ALTER TABLE employees ADD (middle_name VARCHAR2(50))';
        DBMS_OUTPUT.PUT_LINE('MIDDLE_NAME カラムを追加しました');
    ELSE
        DBMS_OUTPUT.PUT_LINE('MIDDLE_NAME カラムは既に存在します');
    END IF;
END;
/

-- カラムの存在を判定する汎用関数
CREATE OR REPLACE FUNCTION column_exists(
    p_table_name  VARCHAR2,
    p_column_name VARCHAR2
) RETURN BOOLEAN IS
    v_count NUMBER;
BEGIN
    SELECT COUNT(*) INTO v_count
    FROM user_tab_columns
    WHERE table_name  = UPPER(p_table_name)
      AND column_name = UPPER(p_column_name);
    RETURN v_count > 0;
END;
/

-- 使い方
BEGIN
    IF column_exists('employees', 'salary') THEN
        DBMS_OUTPUT.PUT_LINE('salary exists');
    END IF;
END;
/

-- カラム数が 50 以上のテーブル（設計見直し候補）
SELECT owner, table_name, COUNT(*) AS column_count
FROM all_tab_columns
WHERE owner NOT IN ('SYS', 'SYSTEM', 'XDB')
GROUP BY owner, table_name
HAVING COUNT(*) >= 50
ORDER BY column_count DESC;

-- DBMS_METADATA でテーブルの完全な DDL を取得
SELECT DBMS_METADATA.GET_DDL('TABLE', 'EMPLOYEES', 'HR') FROM DUAL;

-- 結果: CREATE TABLE 文（全カラム + 制約 + ストレージ属性）

-- 「STATUS」カラムを使っているテーブルを全て洗い出す
-- （STATUS の型変更を検討しているため影響範囲を調査）
SELECT owner, table_name, data_type, nullable, char_length
FROM all_tab_columns
WHERE column_name = 'STATUS'
  AND owner NOT IN ('SYS', 'SYSTEM', 'XDB')
ORDER BY owner, table_name;

-- EMPLOYEES テーブルに HIRE_DATE カラムがあるか
SELECT CASE WHEN COUNT(*) > 0 THEN 'EXISTS' ELSE 'NOT FOUND' END AS result
FROM user_tab_columns
WHERE table_name = 'EMPLOYEES' AND column_name = 'HIRE_DATE';

-- 同じカラム名なのに型が異なるケースを検出（設計不整合）
SELECT column_name, data_type, COUNT(*) AS table_count
FROM all_tab_columns
WHERE owner = 'HR'
GROUP BY column_name, data_type
HAVING column_name IN (
    SELECT column_name FROM all_tab_columns
    WHERE owner = 'HR'
    GROUP BY column_name
    HAVING COUNT(DISTINCT data_type) > 1
)
ORDER BY column_name, data_type;

-- 例: DEPARTMENT_ID が NUMBER と VARCHAR2 で混在 → 設計不整合

-- ALL_TAB_COLUMNS はテーブル + ビューの両方を含む
-- テーブルだけに絞るには ALL_TABLES と JOIN
SELECT c.owner, c.table_name, c.column_name, c.data_type
FROM all_tab_columns c
JOIN all_tables t ON c.owner = t.owner AND c.table_name = t.table_name
WHERE c.column_name = 'EMPLOYEE_ID'
ORDER BY c.owner, c.table_name;

-- ビューだけに絞る場合
SELECT c.owner, c.table_name, c.column_name
FROM all_tab_columns c
JOIN all_views v ON c.owner = v.owner AND c.table_name = v.view_name
WHERE c.column_name = 'EMPLOYEE_ID';