SQL Server のカタログビューからテーブルの定義書情報を取得する

SQLServer にはデータベース内のスキーマ、オブジェクト、スカラー型などの情報を取得するために、システムカタログビューが用意されています。
今回はそのカタログビューを使用して、テーブルと列 (カラム) の情報を取得する SQL を組んでみたいと思います。
カタログビューで取得できる情報を使用すると、テーブル定義書のもとになるデータを作成できます。

カタログビューからは、テーブルと列に関する様々な情報を取得できますが、情報が多すぎるとわかりづらくなるので、基本的な情報のみ取得することにします。

使用するカタログビュー

テーブルと列の情報を取得するために使用するカタログビューは以下になります。

取得するデータは基本的な情報のみに絞り、「テーブル名」「スキーマ名」「テーブルの説明」「列の説明」「列 ID」「列名」「桁数」「Null を許容」「データ型」「プライマリキー」「ID 列の IDENTITY 設定」「初期値 (既定値またはバインド)」の 12 種類にします。

使用するカタログビュー一覧

名称 説明
sys.tables テーブル カタログビュー
sys.schemas スキーマ カタログビュー
sys.extended_properties 拡張プロパティ カタログビュー
sys.columns 列 (カラム) カタログビュー
sys.types 型 カタログビュー
sys.identity_columns ID 列 カタログビュー
sys.index_columns インデックス列 カタログビュー
sys.key_constraints 主キー制約または一意制約 カタログビュー
sys.default_constraints デフォルト制約 カタログビュー

取得するデータ

名称 取得するデータ
sys.tables テーブル名
sys.schemas スキーマ名
sys.extended_properties テーブルの説明
列の説明
sys.columns 列 ID
列名
桁数
Null を許容
sys.types 型名 (データ型)
sys.identity_columns ID 列の IDENTITY 設定
sys.index_columns プライマリキー
sys.key_constraints
sys.default_constraints 初期値 (既定値またはバインド)

テーブル情報

テーブル情報はテーブルカタログビューの sys.tables スキーマカタログビューの sys.schemas 拡張プロパティカタログビューのsys.extended_properties の3つから取得します。

sys.tables

テーブルカタログビューから「オブジェクト ID」と「テーブル名」を取得します。

「オブジェクトID」はオブジェクトを一意に判定するためのキーとなり、別のカタログビューとの結合に使用するので取得しておきます。

sys.schemas

スキーマカタログビューからは「スキーマ名」を取得します。

テーブルカタログビューとスキーマカタログビューを schema_id で内部結合します。

ここではスキーマ名を取得するために sys.schemas を結合していますが、スキーマ名は SCHEMA_NAME 関数で取得することもできます。

sys.extended_properties

拡張プロパティカタログビューからは「テーブルの説明」を取得します。

拡張プロパティカタログビューの class が「1 = オブジェクトまたは列」のデータに絞り込みます。
テーブルカタログビューと拡張プロパティカタログビューを sys.tables.object_id と sys.extended_properties.major_id (table_descriptions.major_id) で外部結合します。(class が 1 の場合、major_id は object_id になります。)
拡張プロパティカタログビューの minor_id に「0」を指定してテーブルの説明に絞り込みます。

テーブル情報の取得は以上です。
次に、列情報を取得します。

列情報

列情報は列 (カラム) カタログビューの sys.columns 型カタログビューの sys.types ID 列カタログビューの sys.identity_columns デフォルト制約カタログビューの sys.default_constraints 拡張プロパティカタログビューの sys.extended_properties インデックス列カタログビューの sys.index_columns 主キー制約または一意制約カタログビューの sys.key_constraints の7つから取得します。

sys.columns

列 (カラム) カタログビューから「オブジェクト ID」「列 ID」「列名」と、「桁数」のもとになる「列の最大長 (バイト単位)」「有効桁数 (数値の場合)」「小数点以下桁数 (数値の場合)」の3つのデータと、「Null を許容するかどうか」「ID 列かどうか」を取得します。

列 (カラム) カタログビュー (sys.columns) はテーブルの情報以外も返すので、この時点では実行結果にビューの情報も含まれます。

sys.types

型カタログビューからは「型名 (データ型)」を取得します。

列カタログビューと型カタログビューは user_type_id で内部結合します。

ここでは型名を取得するために sys.types を結合していますが、型名は TYPE_NAME 関数で取得することもできます。

sys.identity_columns

ID 列カタログビューからは「ID 列の IDENTITY 設定」のもとになる「シード値 (IDENTITY シード)」と「インクリメント値 (ID の増分)」を取得します。

列カタログビューとID 列カタログビューは object_id および column_id で外部結合します。

sys.default_constraints

デフォルト制約カタログビューからは「初期値 (既定値またはバインド)」を取得します。

列カタログビューとデフォルト制約カタログビューを sys.columns.default_object_id と sys.default_constraints.object_id で外部結合します。

sys.extended_properties

拡張プロパティカタログビューからは「列の説明」を取得します。

テーブルの説明の時と同様に、拡張プロパティカタログビューの class が「1 = オブジェクトまたは列」のデータに絞り込みます。
列カタログビューと拡張プロパティカタログビューを sys.columns.object_id と sys.extended_properties.major_id (column_descriptions.major_id) および sys.columns.column_id と sys.extended_properties.minor_id (column_descriptions.minor_id) で外部結合します。

sys.index_columns, sys.key_constraints

プライマリキー情報を取得するために、インデックス列カタログビューと主キー制約または一意制約カタログビューから「プライマリキー列の順番」を取得します。

まず、インデックス列カタログビューと主キー制約または一意制約カタログビューを結合します。
主キー制約または一意制約カタログビューの type に「PK」を指定してプライマリキーに絞り込みます。
インデックス列カタログビューと主キー制約または一意制約カタログビューを index_columns.object_id と key_constraints.parent_object_id および index_columns.index_id と key_constraints.unique_index_id で内部結合します。
結合した結果をサブクエリとして、列カタログビューと object_id および column_id で外部結合します。

ここまでで列情報が取得できたので、先に取得したテーブル情報と合わせます。

テーブル情報 + 列情報

テーブル情報を取得する SQL と 列情報を取得する SQL を組み合わせて1つの SQL にします。

テーブル情報と列情報を合体

テーブル情報と列情報は、tables.object_id と columns.object_id で内部結合します。
これで、列情報がテーブルの情報のみに絞り込まれます。

列情報の変換

新たに取得したい列情報の「桁数」「Null を許容するかどうか」「ID 列の IDENTITY 設定」「初期値」の4つのデータを、先に取得した情報をもとに変換して作成します。

「桁数」は「型名」と「列の最大長 (バイト単位) 」「有効桁数」「小数以下桁数」をもとに変換します。
「Null を許容するかどうか」は Null を許容する場合は「Y (Yes)」許容しない場合は「N (No)」に変換します。
「ID 列の IDENTITY 設定」は「Identity(シード値 (IDENTITY シード), インクリメント値 (ID の増分))」に形に変換します。
「初期値 (既定値またはバインド)」は前後の「(」と「)」を除去します。

最後に不要な列を取り除き、列の順番を入れ替えます。
また、今の状態では全てのテーブルのレコードが取得されるので、スキーマ名とテーブル名を指定できるようにします。

上記の SQL では Microsoft が公開しているデータベースの「AdventureWorks2017」にある dbo.ErrorLog テーブルの情報を取得しています。

実行すると以下のようになります。

SQL Server のカタログビューからはテーブルや列の他にも、ビューやストアドプロシージャをはじめとした様々な情報を取得することができます。例えばこの記事でご紹介した情報の他にもテーブルのインデックスやデータベース自体の情報なども取得可能です。

参考URL

システムカタログビュー

AdventureWorks2017データベースのダウンロード

AdventureWorks のインストールと構成