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

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

情報スキーマ-列名の取得 - HHeLiBeXの日記正道編

この時には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な何か(何)が得られると思うが、疲れるのでやめておく。
ちょっと調べてみたら、次のような記事を見つけたので、参考までに(謎)。

テーブルとビューの定義情報を取得 (sys.tables, sys.columns, sys.objects, sys.extend_properties, sys.constraints, sys.default_constraints) - いろいろ備忘録日記

よくぞここまで、というクエリが書かれていて、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のそれに比べるとずいぶん少ないが。