調査系SQLまとめ

まともなテーブル定義書も何もないDBをぽんと投げられて、色々と調査が必要になる事ってありますよね。

ありますよね！？

あるいは、テーブル定義書はあるんだけどロクにメンテナンスされてなくて、情報が陳腐化してて信頼できない事ってありますよね。

ありますよねぇ！！？？

そんな時に色々と調べて役立ったSQLシリーズ、覚書として纏めておきます。

ちなみにぼくはDB/SQLは苦手分野です。:-p

※今ちょっと環境的にOracleなんで、基本的にOracleメインの話になると思います。

■レベル１：単純にテーブル一覧を出す

まぁまずはシンプルに、テーブル名を列挙するだけ。

SELECT TABLE_NAME
  FROM USER_TABLES
 ORDER BY TABLE_NAME

テーブル名だけじゃ寂しいので、カラムも出そう。

SELECT TABLE_NAME
     , COLUMN_NAME
  FROM USER_TAB_COLUMNS
 ORDER BY TABLE_NAME, COLUMN_NAME

ちなみにこの USER_XXX って言うテーブルは「静的ディクショナリビュー」っていう（ものの一種）らしい。

参考： https://www.shift-the-oracle.com/view/data-dictionary-view/static-data-dictionary-view.html

■レベル２：コメント付きでテーブルとカラムの一覧を出す。

USER_TAB_COMMENTS／USER_COL_COMMENTS ってところに、テーブル・カラムのコメントというメタデータがあるようなので、そいつをくっつけてみます。

SELECT USER_TAB_COLUMNS.TABLE_NAME
     , USER_TAB_COMMENTS.COMMENTS AS TABLE_COMMENT
     , USER_TAB_COLUMNS.COLUMN_NAME
     , USER_COL_COMMENTS.COMMENTS AS COLUMN_COMMENT
  FROM USER_TAB_COLUMNS 
  LEFT JOIN USER_TAB_COMMENTS
         ON USER_TAB_COLUMNS.TABLE_NAME  = USER_TAB_COMMENTS.TABLE_NAME 
  LEFT JOIN USER_COL_COMMENTS
         ON USER_TAB_COLUMNS.TABLE_NAME  = USER_COL_COMMENTS.TABLE_NAME
        AND USER_TAB_COLUMNS.COLUMN_NAME = USER_COL_COMMENTS.COLUMN_NAME
ORDER BY TABLE_NAME, COLUMN_NAME

■レベル３：各テーブルのPKを調べる

USER_CONSTRAINTS って所に制約系の情報が入ってるので、そいつをくっつけて、各テーブルのPKを調べてみます。

SELECT USER_CONS_COLUMNS.TABLE_NAME
     , USER_CONS_COLUMNS.COLUMN_NAME AS PRIMARY_KEY

  FROM USER_CONS_COLUMNS 

 INNER JOIN USER_CONSTRAINTS
    ON USER_CONS_COLUMNS.CONSTRAINT_NAME 
     = USER_CONSTRAINTS.CONSTRAINT_NAME
   AND USER_CONSTRAINTS.CONSTRAINT_TYPE = 'P'

 ORDER BY TABLE_NAME, COLUMN_NAME

今回はPKを調べたので CONSTRAINT_TYPE：P を検索しましたが、
それ以外にも制約があるので、下記ページなんかを参考に。

参考： http://oracle.se-free.com/dic/E4_constrains.html

■レベル４（番外編）：各テーブルのPKを調べてテーブル単位で纏める

LISTAGG って言う集計関数があって便利なので、こいつを使います。

SELECT TABLE_NAME
     , LISTAGG(PRIMARY_KEY, ',') within group ( order by null ) AS PRIMARY_KEYS
FROM (
    SELECT USER_CONS_COLUMNS.TABLE_NAME
         , USER_CONS_COLUMNS.COLUMN_NAME AS PRIMARY_KEY

      FROM USER_CONS_COLUMNS 

     INNER JOIN USER_CONSTRAINTS
        ON USER_CONS_COLUMNS.CONSTRAINT_NAME 
         = USER_CONSTRAINTS.CONSTRAINT_NAME
       AND USER_CONSTRAINTS.CONSTRAINT_TYPE = 'P'
) X
GROUP BY TABLE_NAME

例えばこんな感じだったとしたら

TABLE_NAME	PRIMARY_KEY
hoge	a
hoge	b
hoge	c
moge	x
moge	y
piyo	z

こう取れる。

TABLE_NAME	PRIMARY_KEYS
hoge	a,b,c
moge	x,y
piyo	z

業務実装ではあまり使わないかも知れないけど、ちょっとしたツール作ったり、SQLで中間データで引っ張って来る時とか、結構便利よね。

LISTAGG関数 についてはこちら。

参考： https://www.shift-the-oracle.com/sql/aggregate-functions/listagg.html

個人的には within group ( order by null ) は省略したいなぁ、、、。

■レベル５（最終奥義）：横断検索

いわゆる「横断検索」とか「全文検索」って言われてる（らしい）やつ。

要するに「全テーブル、全カラムを対象に、任意の文字列を探す」というもの。

たまにそういう作業依頼が来たり、或いはノーヒントのシステム調査の為にそう言う事をしなきゃならない場面ってたまに出て来るよね。

凄く便利なSQLがあったので、まるっと転記して来ました。

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;

何か未使用変数とかも残っててアレだけどそのまま転記しました。

※出典は後述する「参考情報」を参照。

使用方法

見りゃ解ると思うけど、' like ''%あ%''';の部分を任意の検索ワードに置き換得て使います。

このオリジナルの実装では %CHAR% で対象を絞ってるので、
CHARもVARCHAR2も対象になるんで、
それぞれの要件に応じてこれを切り替えれば良い。
（固定長のCHARはコード系のカラムに限定されてて、検索対象に入れたくない、とかね）

後は １件以上ある場合の件数を出力する という実装になってるので、この辺も適宜書き換えて、それぞれ都合のいい形で利用。

注意事項

dbms_output.put_line でデバッグ出力してるけど、
ぼくが使ってるクライアントツールだと、
先に以下のコードを埋めておかないと出力されなかった。

dbms_output.enable( 5000 ); --5000は適当。

他のクライアントツールを使ってる人も put_line が効かないって話はよく聞く。
その場合、以下のコマンドを先に叩いておくと幸せになれるらしい。

$ SET SERVEROUTPUT ON;

参考情報（出典）

別解

OPEN CURSOR してゴチャゴチャやるのがアレだって言う人は、
一時テーブル作ってそこにガシガシ SELECT INSERT すると言う方法もある。

その場合の参考情報はこちら。

ちなみにぼくはこっちの方が（カーソル開くより）好き。

http://sql55.com/query/search-string-in-all-columns-of-all-tables.php

※ このサイトは SQL Server の例だけど、基本的な実現方法は変わらないよね。

■究極兵器：A5 SQL Mk2

神ツール。

テーブル定義書やER図

上の方でSQLたくさん載せてきたけど、テーブル定義書出したいだけならこのツールで事足りるどころかER図だって作れちゃうし、大半の要求がこのツール１本で片付くマジやばい。

導入手順

Qiitaに導入手順を纏めてくれている神もいるやばい。

まぁぶっちゃけ導入手順なくてもノリで入れてサクッと使えるくらいにシンプルで洗練されたツール。

なのに高機能で出来る事はめちゃくちゃ多いし、プロパティ設定で弄れる項目も多く自由度が高い。

インテリセンスが効く

神。

VisualStudio の Intelli-sence みたいな、コード入力補完機能（[Ctrl] + {SPACE} のアレね）が使える。

これの何が優秀かって、実テーブルだけじゃなくて、
副問合せみたいなぐちゃっとしたものでもSQL結果セットの形から推論して入力補完効かせてくれるからヤバい。

しかも、そんな動的なアレをやってるのに動作が軽くてサクサク。

有償無償問わず、今まで使ったSQLクライアントツールの中ではダントツで優秀なんじゃないかな。

ほんと神ツール。

ということで

調査系SQL纏めと見せかけた A5 SQL Mk2 紹介記事でした。:-)