SQL

全テーブル・全列の値の検索方法

特定の列名を持つテーブルを検索するのはそこまで難しくはないでしょう。
「カラム名 検索」などでググれば期待通りの結果が得られると思います。

では特定の値が登録されているカラムを調査したい場合、どうすれば良いか・・・
oracleであればPLSQLを用いた下記解決方法があります。

declare
 sSQL VARCHAR2(128);
 sDATA VARCHAR2(500);
 type cursor_type is ref cursor;
 cur_search cursor_type;
 vCount INTEGER;
 ERR_CODE NUMBER := 0;
 ERR_MSG VARCHAR(255);
begin
 for vRec in (select COLUMN_NAME,TABLE_NAME from USER_TAB_COLS where DATA_TYPE like '%CHAR%')
 loop
  begin
    open cur_search for 'select count(*) as cnt from ' || vRec.TABLE_NAME || ' where ' || vRec.COLUMN_NAME || ' like ''%あ%''';
        fetch cur_search into vCount;
        if vCount > 0 then
          dbms_output.put_line(vRec.TABLE_NAME || '.' || vRec.COLUMN_NAME || ':' || vCount || '件あり');
        end if;
        close cur_search;
  exception
   WHEN OTHERS THEN
    ERR_CODE := SQLCODE;
    ERR_MSG  := SUBSTRB(SQLERRM,1,255);
    dbms_output.put_line('error:' || ERR_CODE || ' ' || ERR_MSG || ' ' || vRec.TABLE_NAME || '.' || vRec.COLUMN_NAME);
  end;
 end loop;
end;

ケースとしては、顧客コードが登録されているテーブルが知りたい。
ただ、顧客コードが「CUSTOMER_ID」だったり、「CLIENT_CD」だったり、「KOKYAKU_CD」だったりバラバラだぞ・・・
なんて時に調査に役立つかもしれません。（完全に設計ミスですけどね・・・）
上記のケースですと、「%あ%」の部分を顧客コードでしか使われてなさそうな値にするだけでも目的のテーブル、カラムが調査可能だと思います。

注意が必要なのは「値」での検索のため、
どのテーブルにでも存在しそうな値、例えば日付やログインユーザIDなどで検索すると、不要な結果も取得してしまうことですね。

その場合検索条件に更新日付、更新者は除外するなどの条件を付け加えると良いでしょう。

 

こんなケースで使用しました

現場での話になります。とあるコード・・・仮に「商品コード」とでもしておきましょうか。
その商品コードを９桁から６桁で管理したいという内容の要望がありました。
ところがその商品コードがどのテーブルで使われているか全部は把握できていないという状態。
さて困った。

そこで商品マスタの商品コード存在分すべてを全部上記のPLSQLで調査しました。商品マスタのカーソルループで上のPLSQLを実行する感じですね。もちろん、全テーブルの規模によりどれくらい時間が掛かるかは検討がつきませんので要注意です。

後は人それぞれやり方があると思うのですが、
自分のやり方としては

  1. 上記PLSQLにて「,」区切りで必要な情報をログに吐き出すようにする。（商品コード、テーブル、カラム名など）
  2. EXCELに貼り付けて桁数変換対象外で良さそうなテーブルを除外
  3. ２番で変換対象となったテーブルのカラムを更新するSQL文作成（EXCEL内でSQL文書いて、動的に変わる部分だけセル指定して作りました）
  4. SQL実行

終わり！って感じです。
調査結果の対象テーブルが思ったより多くなかったのでできた荒業でした笑
あとはこの「商品コード」を過去分含めて全部変えちゃっていいよと言われていたのが大きいですね。
もしこれが「○月のデータから～」とか言われていたら影響範囲が分からないので断っていたかもしれないです笑

工夫次第でかなり使えると思うので、皆さんも困ったときには調査に使用してみてください。

-SQL
-