HHeLiBeXの日記 正道編

日々の記憶の記録とメモ‥

情報スキーマ - 表名と列名の取得

SQL SQL Server

以前、いくつかのRDBMSについて、列名を取得するためのクエリをまとめてみた。

この時にはSQL Serverは未導入だったような気がするので調べてみた。
まず、列名について前回とほぼ同じことをSQL Server 2005 Expressと2008 Expressに対して行ってみる。
初期化：

CREATE TABLE hoge1(val1 INT) ;
CREATE TABLE HOGE2(VAL2 INT) ;
CREATE TABLE "hoge3"("val3" INT) ;
CREATE TABLE "HOGE4"("VAL4" INT) ;

後始末：

DROP TABLE "HOGE4" ;
DROP TABLE "hoge3" ;
DROP TABLE HOGE2 ;
DROP TABLE hoge1 ;

で、肝心の列名を取得するためのクエリだが、初期化処理前に何気なく「select * from information_schema.columns」と打ったら、空の表が返ってきた。ということは、標準SQL通りに実装されている？ということで、ちゃんと調べてみると：


SQL Server 2005から、標準SQL準拠の「情報スキーマ」（information_schema）が実装されています。
ということらしい。(PostgreSQLと比べると列数がかなり少ない気がするが気にしない(待て))
ちなみに、上記サイトに「大文字にしないとエラー」ということが書かれているが、自分は別のクライアント(Epictetus)からクエリを投げ、小文字でもちゃんと通った。
ということで、本処理で投げるクエリは次のとおり。
SQL Server (2005, 2008)

SELECT TABLE_NAME, COLUMN_NAME
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_NAME IN
        ('hoge1', 'HOGE1',
         'hoge2', 'HOGE2',
         'hoge3', 'HOGE3',
         'hoge4', 'HOGE4') ;

実行結果。

TABLE_NAME COLUMN_NAME
1 hoge1 val1
2 HOGE2 VAL2
3 hoge3 val3
4 HOGE4 VAL4

まさかの「列名の大文字小文字は書いたとおり」というパターン。
ちなみに、先のSELECT文のWHERE句がなくても同じ結果が返ってくる。どうやら、接続しているデータベース内にあるユーザテーブルの情報しか取得できないらしい。


続いて表名なのだが、その前にちょっと余談。
上記サイトで「SYS.COLUMNS」というのがあるということだったので、この中身ものぞいてみると、列名は分かるが、それに対応する表名が分からない。代わりに「object_id」という列がある。ならば「SYS.TABLES」も、と思って試してみたらこっちも通った。
中身を見てみると、先ほどの「SYS.COLUMNS」と一致する「object_id」が「SYS.TABLES」の中にあり、表名も対応している。
がんばればINFORMATION_SCHEMA.COLUMNSな何か(何)が得られると思うが、疲れるのでやめておく。
ちょっと調べてみたら、次のような記事を見つけたので、参考までに(謎)。

よくぞここまで、というクエリが書かれていて、Epictetusでは(長すぎて？)実行できませんでした(謎)。


表名は、INFORMATION_SCHEMA.COLUMNSの中にも出てくるが、表名だけが必要ならINFORMATION_SCHEMA.TABLESからでも取得できる。

SELECT * FROM INFORMATION_SCHEMA.TABLES;

結果。

TABLE_CATALOG TABLE_SCHEMA TABLE_NAME TABLE_TYPE
sandbox dbo hoge1 BASE TABLE
sandbox dbo HOGE2 BASE TABLE
sandbox dbo hoge3 BASE TABLE
sandbox dbo HOGE4 BASE TABLE

まぁ、格納されている情報は、標準SQLのそれに比べるとずいぶん少ないが。