ORACLEでとあるカラムを所持しているテーブルの物理名と論理名を検索したい

やりたいこと

とあるカラムを所持しているテーブルの物理名と論理名が知りたい。

どうすれば良いのか考えて、最初はDDLからカラムの物理名か論理名でGrepして、そのテーブル物理名からテーブルの論理名を出して…とやるしかないのかなぁ なんて考えたのですが、調べる対象のテーブル総数がすごい数だったので嫌でした。

なによりもっと良い方法があるはずだ、だってORACLEだぜ?って思いました。 ORACLEのリファレンスを調べてみると良い方法がありました。

SQLでテーブルやカラムの定義情報を取得する

下記のSQLを実行で解決しました。

SELECT
  T.TABLE_NAME AS "テーブル物理名"
  , TC.COMMENTS AS "テーブル論理名"
  , C.COLUMN_NAME AS "カラム物理名"
  , CC.COMMENTS AS "カラム論理名" 
FROM
  USER_TABLES T 
  INNER JOIN USER_TAB_COMMENTS TC 
    ON (T.TABLE_NAME = TC.TABLE_NAME) 
  INNER JOIN USER_TAB_COLUMNS C 
    ON (T.TABLE_NAME = C.TABLE_NAME) 
  INNER JOIN USER_COL_COMMENTS CC 
    ON ( 
      T.TABLE_NAME = CC.TABLE_NAME 
      AND C.COLUMN_NAME = CC.COLUMN_NAME
    ) 
WHERE
  C.COLUMN_NAME IN ('検索したいカラム物理名') 
ORDER BY
  T.TABLE_NAME

WHERE句には条件としてカラムの物理名を書きましたが LIKEにして論理名を指定でも、また違った検索のやり方ができそうです。

解説

簡単に解説します。

まずFROM句。ORACLEのリファレンスに全部書いてあることですが…

表名 説明
USER_TABLES ユーザーが所有する全ての表を示す
USER_TAB_COMMENTS ユーザーが所有する表およびビューについてのコメントを示す
USER_TAB_COLUMNS ユーザーが所有する表、ビューおよびクラスタの列を示す
USER_COL_COMMENTS ユーザーが所有する表およびビューの列についてのコメントを示す

上記の表からINNER JOINで繋げました。 SELECT句は単純に欲しい情報を取り出しているだけです。(今回で言えばテーブルの物理名と論理名、合わせてカラムの物理名と論理名も出しています)

あとはWHERE句で条件指定とORDER BYでテーブルの物理名で並べました。

参考

Oracle® Databaseリファレンス


その他にも様々な定義情報を取り出すことができるようです。 こんな風に定義情報を取得することができるのは初めて知りました。