【SQL Server】テーブルのメタデータを取得する

SQL Server snippet

色々と作業していたらテーブル設計書とレイアウトが全然違うテーブルが存在することに気づきました。

流石にテーブル名ぐらいはあってるんですが、見たことないカラムがもりもり追加されています。一体何を見てこのテーブルが作られたのか皆目検討もつきません。担当者は既に失踪してるので責め立てることも出来ません。まぁ、よくあることです。

とりあえず今のレイアウトと設計書のレイアウトのDiffをとらないとどうにもならないので、テーブルのメタデータを取得するSQLを書きます。こうやって余計な仕事が増えていくんですよねぇ。

オブジェクトカタログビューをながめる

Oracleなんかでもそうですが、SQL Serverのオブジェクト（テーブル、ビュー、インデックス、ストアドプロシージャ etc…）の情報はちゃんとDBに保管されているし、SELECTの権限があれば普通に見れます。

そのほとんどが見れるのがこのオブジェクトカタログです。とりあえずsys.objectsから見ていくのが定石です。

このsys.objects.object_idと他のカタログビューのobject_idをJOINしてもりもり情報をかき集めていくことになります。今回はテーブルだけ見れればいいので、sys.columnsと結合します。

SELECT
 --TODO:メタデータをあつめる
FROM
 sys.objects obj
 INNER JOIN sys.columns col
  ON obj.object_id = col.object_id
WHERE
 -- U = テーブル (ユーザー定義) 
 obj.type = 'U'

ちなみに、sys.tablesと言うビューもあります。これはsys.object.type = ‘U’で抽出済みだったり、他の情報が付与されてたりと、そこそこ便利です。今回はどっちでもいいんですけどね。

必要な情報をあつめる

じゃあ先ほどのSQLをベースに必要な情報を集めていきましょう。

SELECT
 obj.name [テーブル名]
 , col.name [カラム名]
 , col.max_length [最大桁数]
 , col.precision [有効桁数]
 , col.scale [小数点以下桁数]
 , col.is_nullable [Null許可]
FROM
 sys.objects obj
 INNER JOIN sys.columns col
  ON obj.object_id = col.object_id
WHERE
 -- U = テーブル (ユーザー定義) 
 obj.type = 'U'
ORDER BY
 obj.name
 , col.column_id

sys.columnsのcolumn_idにカラムの順番があるので、こいつでORDER BYをかけると定義順になってくれます。

で、sys.columnsはデータ型の情報をそのままでは持っていないので、sys.typesと結合してとってきます。

SELECT
 obj.name [テーブル名]
 , col.name [カラム名]
 , typ.name [型名]
 , col.max_length [最大桁数]
 , col.precision [有効桁数]
 , col.scale [小数点以下桁数]
 , col.is_nullable [Null許可]
FROM
 sys.objects obj
 INNER JOIN sys.columns col
  ON obj.object_id = col.object_id
 INNER JOIN sys.types typ
  ON col.user_type_id = typ.user_type_id
WHERE
 -- U = テーブル (ユーザー定義) 
 obj.type = 'U'
ORDER BY
 obj.name
 , col.column_id

まとめ

これで後は欲しいテーブルのあるDBでSQLを投げるだけです。

実際にはobj.nameでWHEREをかけるなどすべきでしょう。ストアドファンクション化しちゃってもいいかもしれません。

私はとりあえず次にこいつを吐き出すスクリプトを組まなきゃですね…。やれやれ。

参考