複数人で同時に開発を行うような案件ではカラム名がすぐにわからないことなどがありますが、そういった場合に一覧を出力してくれるのがシステムビューです。
今回の記事ではビューの定義と二つのシステムビュー「sys.objects」と「sys.columns」について解説をしていこうと思います。
この方法をマスターすれば簡単にカラム名一覧を列挙することが出来るので、是非最後まで読んでみてください。
ビューとは
SQL Serverにはシステムビューの他にも、インデックスビューやパーティションビューなどがありますが、そもそもデータベースにおける「ビュー」とは何でしょう？
ビューとは簡潔に言い表すと仮想テーブルだと思ってください。
実際に存在するデータベーステーブルではないので、データなどが入っているわけではありません。
ビューをSELECT文で定義すると、実テーブルから必要なデータが抽出され、その情報を基にした仮想テーブルがメモリに蓄積されます。
これによって独自のテーブルがあるかのように見ることが出来る仕組みとなっています。
ビューのメリット
定義というだけあって、ビューはクエリで自由に作ることが可能です。
また聞くだけだとちょっと理解が難しいかもしれませんが、ビューでビューを定義することも可能です。
このことから、毎回記述するのことが面倒で複雑なクエリーがあったとしても、これをビューとして定義しておけば、その後は簡単なクエリーで呼び出しが出来るようになります。
規模が大きくなればなるほど開発に係る労力は計り知れませんので。その効果も絶大となってきます。
また他にも、一般ユーザーにデータベース内のデータを使用させるような場合には、ビューを使用することで開発側が操作することも可能です。
通常、一般ユーザーには権限の付与は出来ない為、データベース内の見せたくないデータなどを選んで隠すことは出来ません。
しかしビューを作成する段階で見せたくないデータだけを排除した仮想テーブルを作成すれば、このビューを使わせることで間接的にテーブルを操作することが可能になります。
sys.objects
「sys.objects」とは何か？について初心者の方にわかりやすく全てを説明することは非常に難しいのですが、簡単に言ってしまうと「データベース内にある全オブジェクト毎の情報を管理できるモノ」です。
SQLでいうオブジェクトとは、テーブルや列の他にもスキーマやジャーナル、カタログ、制約、ストアドプロシージャ、関数、変数などありとあらゆるものを指します。
この記事ではsys.objectsを使用することで、テーブルや列そのものを検索することが出来るとだけ覚えておいてください。
この「sys.objects」を使い慣れてきたときに改めてリファレンスを読めば、もう少し掘り下げて理解できると思います。
sys.objectsの使い方
sys.objectsには様々な項目が設けられていますが、今回はこの項目の中の「type」を利用します。
このtypeはオブジェクトの種類を指定する項目で、テーブルの検索をする場合、typeは「U」となります。
仮にデータベース内のテーブルをくまなく検索したい場合には次のように記述します。
SELECT * FROM sys.objects WHERE type = 'U'
sys.column
sys.columnsは、ビューやテーブルなどの列を持つオブジェクトから列の情報を返します。
列を持つオブジェクトには次のようなものがあります。
- テーブル値アセンブリ関数 (FT)
- インラインテーブル値 SQL 関数 (IF)
- 内部テーブル (IT)
- システムテーブル
- テーブル値 SQL 関数 (TF)
- ユーザーテーブル (U)
- ビュー (V)
またこのsys.columnsにも非常に多くの項目が用意されているため、 全て覚えようとするのではなく、利用するケースによってリファレンスに目を通しながら選択してください。
sys.columnsを使った例として、テーブルを指定してカラム一覧を取得する方法が次のコードです。
SELECT * FROM sys.columns WHERE object_id = ( SELECT object_id FROM sys.tables WHERE name = 'テーブル名' )
必要な情報を取得する
「sys.objects」と「sys.columns」についての解説である程度理解してもらえたともいますが、この二つの情報をそのまま取得することは無駄が多すぎるため、情報を選別して取得する必要があります。
例えば、基本的なところでカラムに対して最も必要と思われるのは次の3つの項目ではないでしょうか？
- 属性
- 長さ
- NULL許可
仮にこの3項目が欲しい場合にsys.columnsでそのまま取得してしまうと、情報量多さから逆に解り難くなるので次のように整形します。
USE sample_db
SELECT
t.name AS テーブル名,
c.name AS 項目名,
type_name(user_type_id) AS 属性,
max_length AS 長さ,
CASE
WHEN
is_nullable = 1
THEN
'YES'
ELSE
'NO'
END AS NULL許可
FROM
sys.objects t
INNER JOIN sys.columns c ON
t.object_id = c.object_id
WHERE
t.type = 'U'
AND
t.name IN ('sample_table', 'stuff_table')
ORDER BY
c.column_id
GO
テーブル名 項目名 属性 長さ NULL許容
--------------- ---------- ------------ ------- ----------
sample_table id int 4 NO
stuff_table stuff_id int 4 NO
stuff_table stuff_name nchar 20 YES
sample_table name nchar 60 YES
sample_table age int 4 YES
stuff_table stuff_age int 4 YES
stuff_table stuff_no nchar 40 YES
sample_table sex nchar 60 YES
sample_table job nchar 60 YES
ポイントは「sys.objects」と「sys.columns」両方のビューをINNER JOINで紐づけて一つの仮想テーブルにしている点です。
sys.columnsのみではテーブル名は取得できない為、複数のテーブルの情報を同時に取得するような場合には不十分なリスト(同一列名がある場合などが挙げられる)となります。
しかしsys.objectsと併用することで、sys.objectsの持つ項目も使えるようになるため、テーブル名も同時に取得することが出来ます。
結果、テーブル名と項目名に続いて先ほど挙げた3項目をリスト化できるため、非常に扱いやすい形となっていることが確認できます。
まとめ
今回は列名の取得について、実践で使えるレベルのサンプルコードを交えながら解説を行ってきましたが、いかがでしたか？
初心者の方にとってはリファレンスのような解り難い説明を読むだけでも苦労すると思いますので、こういった応用まで頭が回らないことが多いと思います。
しかし実際には単独で使用するケースはあまりなく、それぞれを組み合わせて使うことで一つの形となるため、出来るだけこういったケースに慣れていくようにしましょう。
