データベースの全項目を網羅的に検索：Oracleデータベースの全テーブル、全フィールドを特定の値で検索

2024-04-07

Oracleデータベースの全テーブル、全フィールドを特定の値で検索する

方法

ALL_TAB_COLUMNS と USER_TAB_COLUMNS を使う

この方法は、すべてのユーザーのテーブルを検索するか、現在のユーザーのテーブルのみを検索するかを選択できます。

すべてのユーザーのテーブルを検索

SELECT table_name, column_name
FROM ALL_TAB_COLUMNS
WHERE column_name LIKE '%検索したい値%';

SELECT table_name, column_name
FROM USER_TAB_COLUMNS
WHERE column_name LIKE '%検索したい値%';

DBMS_METADATA を使う

この方法は、より柔軟な検索オプションを提供します。

DECLARE
  l_cursor DBMS_METADATA.REFCURSOR;
  l_table_name VARCHAR2(30);
  l_column_name VARCHAR2(30);
BEGIN
  DBMS_METADATA.OPEN_CURSORS(l_cursor);
  FOR rec IN (
    SELECT table_name, column_name
    FROM DBMS_METADATA.GET_ALL_TAB_COLUMNS
  ) LOOP
    l_table_name := rec.table_name;
    l_column_name := rec.column_name;
    DBMS_OUTPUT.PUT_LINE(l_table_name || '.' || l_column_name);
  END LOOP;
  DBMS_METADATA.CLOSE_CURSORS(l_cursor);
END;

SQL*Plus スクリプトを使う

この方法は、複雑な検索条件を設定する場合に便利です。

@search_all_fields.sql

search_all_fields.sql

SET SERVEROUTPUT ON SIZE 1000000

DECLARE
  l_table_name VARCHAR2(30);
  l_column_name VARCHAR2(30);
  l_value VARCHAR2(2000);
BEGIN
  FOR rec IN (
    SELECT table_name, column_name, data_type
    FROM user_tab_columns
    WHERE data_type IN ('VARCHAR2', 'CHAR', 'NVARCHAR2', 'NCHAR')
  ) LOOP
    l_table_name := rec.table_name;
    l_column_name := rec.column_name;
    SELECT value
    INTO l_value
    FROM l_table_name
    WHERE l_column_name LIKE '%検索したい値%';
    IF l_value IS NOT NULL THEN
      DBMS_OUTPUT.PUT_LINE(l_table_name || '.' || l_column_name || ': ' || l_value);
    END IF;
  END LOOP;
END;

注意点

大量のデータがある場合は、検索処理に時間がかかる場合があります。
検索条件によっては、意図しない結果が返される可能性があります。
テスト環境で十分に確認してから本番環境で実行してください。

ALL_TAB_COLUMNS と USER_TAB_COLUMNS を使う

-- すべてのユーザーのテーブルを検索
SELECT table_name, column_name
FROM ALL_TAB_COLUMNS
WHERE column_name LIKE '%検索したい値%';

-- 現在のユーザーのテーブルのみを検索
SELECT table_name, column_name
FROM USER_TAB_COLUMNS
WHERE column_name LIKE '%検索したい値%';

DBMS_METADATA を使う

DECLARE
  l_cursor DBMS_METADATA.REFCURSOR;
  l_table_name VARCHAR2(30);
  l_column_name VARCHAR2(30);
BEGIN
  DBMS_METADATA.OPEN_CURSORS(l_cursor);
  FOR rec IN (
    SELECT table_name, column_name
    FROM DBMS_METADATA.GET_ALL_TAB_COLUMNS
  ) LOOP
    l_table_name := rec.table_name;
    l_column_name := rec.column_name;
    DBMS_OUTPUT.PUT_LINE(l_table_name || '.' || l_column_name);
  END LOOP;
  DBMS_METADATA.CLOSE_CURSORS(l_cursor);
END;

SQL*Plus スクリプトを使う

@search_all_fields.sql

SET SERVEROUTPUT ON SIZE 1000000

DECLARE
  l_table_name VARCHAR2(30);
  l_column_name VARCHAR2(30);
  l_value VARCHAR2(2000);
BEGIN
  FOR rec IN (
    SELECT table_name, column_name, data_type
    FROM user_tab_columns
    WHERE data_type IN ('VARCHAR2', 'CHAR', 'NVARCHAR2', 'NCHAR')
  ) LOOP
    l_table_name := rec.table_name;
    l_column_name := rec.column_name;
    SELECT value
    INTO l_value
    FROM l_table_name
    WHERE l_column_name LIKE '%検索したい値%';
    IF l_value IS NOT NULL THEN
      DBMS_OUTPUT.PUT_LINE(l_table_name || '.' || l_column_name || ': ' || l_value);
    END IF;
  END LOOP;
END;