SQL全テーブル検索

Question

データベースの全テーブルから、特定の文字列検索したいのですが、
何かいい方法はありませんか？

例えば、顧客から、
システムに表示されている「首相」という文字を
すべて「総理」という表記に変更したいという要望があったとします。
システム上「首相」と表示している画面は複数あります。

「首相」という文字列は、すべてデータベースで設定されているとします。
データベースには複数テーブルがありますが、どのテーブルのどのカラムで
「首相」という文字列を設定しているかはわかりません。
システムは大規模なので、仕様書ベースで影響範囲を調査するのは困難とします。

そこで、どのテーブルのどのカラムに「首相」という文字列が
設定されているのかを確認したいのですが、
何かいい方法はありませんか？

Answer 1

質問者様の望んでおられる、
ピンポイントで検索文字列が設定されたテーブル内のカラムの特定はなかなか難しそうなので(ストアドでバッチ化すればもしかすると不可能ではない？)、

当方の思いつく限りで何個か提案してみます。

#データベース単位でデータエクスポートして検索文字列を探す
データベースのdump機能などでテーブルとデータをエクスポートし、
それをエディタのGrep検索などで当たりをつけていく方法です。

DBMSによるかもですが、
dumpにはINSERT文としてごと吐き出す等の機能もあるので、
格納されたテーブルとカラムの特定は比較的容易かと思います。

少なくとも仕様書ベースに探すよりは時間が削減できるかと思われます。

#ストアドを作成してゴリゴリと
これもDBMSごとに依存しそうですが、
ストアドで「首相」が設定されたテーブル・レコードをテキスト形式で吐き出すくらいは出来るかもしれません。
0. 検索したいデータベース内のテーブルリストをDBMSのカタログより取得
0. テーブルごとのカラムリストを取得
0. 動的にSQLを作成し、検索結果をテキストファイルか何かに落とす

3番目にて組み立てるSQLは以下のようなイメージ

• テーブル定義

CREATE TABLE table_1(
    HOGE VARCHAR(10) PRIMARY KEY
,   FUGA VARCHAR(4)
,   PIYO INT
)

• 動的SQL

SELECT
    t.*
FROM
   table_1 t
WHERE
    '首相' IN (t.HOGE, t.FUGA) -- この箇所で指定するカラムは文字列型でないとエラーとなる

ただ上記のようなストアドを作るのもなかなか骨が折れるかもかもしれませんし、
テーブル数、データ数が莫大ともなると実行自体がなかなか終わらないと思われるので(途中でこけたりしたら悲惨)、
採用するに当たっては慎重に検討してください。

#ただ本来の理想を言うと
データがあちこちに散らばっている現状が非常にまずいです。

今回は一時対応でことなきを得たとしても、
同じような状況が再度起こった際再び対応時間が取られるとなると、
非効率極まりません。

システム規模が大きいということから、
大変であろうことは承知で申し上げますと、
例えば項目名マスタとか１つのマスタテーブルをこしらえて、
システムの各機能はそのテーブルを参照するように変更した方が合理的です。

今すぐ対応するというのは困難かもしれませんが、
長期的に見るとテーブル構成の見直しが一番最適解となるので、
そちらも考慮に入れてみてはいかがでしょうか？

データベースの本来の姿としては、
テーブルとカラムをもってデータが識別されるのであって、
登録されたデータからテーブルなりカラムを識別するものではないため、
今回のような処理では、DB単体では不得手となるのは致し方ない所です。

Comments

[退会済みユーザー]

回答ありがとうございます。
dumpファイルをgrepで検索するの方法を
試してみようと思います。

Answer 2

顧客名が変更になって顧客マスタを変更すれば済むだけみたいな案件で、正規化以前のレベルで顧客名があちこちに散らばっているとんでもないテーブル設計で同様のUPDATE文を書いて対応したことがあります。
顧客名の列名がCUSTOMER_NAMEだったか？で統一されていたので、ALL_TAB_COLUMNSでCUSTOMER_NAMEが使われているテーブルをUPDATEするSQLをSPOOLしてリダイレクトするだけでしたが。

Answer 3

－評価だったので、少し編集。

データベースの全テーブルから、特定の文字列検索したいのですが、
何かいい方法はありませんか？
システム上「首相」と表示している画面は複数あります。
「首相」という文字列は、すべてデータベースで設定されているとします。

以下の手順でしょうか。

１．全テーブルのVARCHAR等の全カラムについてのみ、以下のようなSQLで抽出する。

SELECT カラム名　as BEFORE
 , REPLACE(カラム名,'首相','総理') as AFTER
FROM テーブル名
WHERE カラム名 like '%首相%'
;

２．上記SQLで引っかかったカラムに対して、以下のSQLを実行する。

UPDATE テーブル名
 カラム名　= REPLACE(カラム名,'首相','総理')
WHERE カラム名 like '%首相%'
;

上記SQLの作り方は、言語によって様々なので指定してほしいところ。
desc テーブル名;　等で
テーブルとカラムの一覧を取得してEXCEL等に貼り付けて、
="SELECT "&A2&" FROM "&A1&" WHERE "&A2&" like '%首相%';"
とコピーすればすぐに作れる。

まとめて実行したい場合、SQL文の最後に実行結果を
テーブル名＋カラム名のファイル名でファイル出力しても良いでしょう。
（Oracleなら、USER_OBJECTS等でもテーブル名とカラム名は取得できる。）

なお、本来であれば、たとえ1000個以上のテーブルがあっても、
どんなテーブルがあり、どんなデータが格納されているかを把握したいところ。