前に、テーブルとカラムの説明を取得するSQLを書いていたのですが、説明プラス定義情報を
いろいろ取得するSQLです。自分自身が忘れない内にメモメモ。
テーブルの定義情報取得
USE AdventureWorks GO /* テーブル定義情報を取得 */ SELECT DB_NAME() AS db_name ,SCHEMA_NAME(tables.schema_id) AS schema_name ,tables.name AS table_name ,tables.type_desc AS table_type ,extProp.value AS table_comment FROM sys.tables tables /* コメントデータは拡張プロパティシステムビューに存在する */ LEFT OUTER JOIN sys.extended_properties extProp ON /* テーブルの場合、classの値は常に1 (OBJECT_OR_COLUMN) */ extProp.class = 1 /* テーブルのobject_idはmajor_idと対応する */ AND tables.object_id = extProp.major_id /* テーブルの場合、minor_idの値は常に0 */ AND extProp.minor_id = 0 ORDER BY db_name, schema_name, table_name GO
カラムの定義情報を取得
USE AdventureWorks GO /* カラム定義情報を取得 */ SELECT DB_NAME() AS db_name ,SCHEMA_NAME(tables.schema_id) AS schema_name ,tables.name AS table_name ,columns.name AS column_name ,columns.column_id AS ordinal_position /* PKか否かの判別フラグ */ ,CAST(CASE WHEN pk_cols.key_ordinal IS NOT NULL THEN 1 ELSE 0 END AS bit) AS is_primary_key ,pk_cols.constraint_name AS pk_constraint_name ,pk_cols.key_ordinal AS pk_key_ordinal /* ユニークキー(UQ)か否かの判別フラグ */ ,CAST(CASE WHEN uq_cols.key_ordinal IS NOT NULL THEN 1 ELSE 0 END AS bit) AS is_unique_key ,uq_cols.constraint_name AS uq_constraint_name ,uq_cols.key_ordinal AS uq_key_ordinal ,TYPE_NAME(columns.system_type_id) AS column_data_type ,defaultConstraints.definition AS column_default ,columns.is_nullable AS is_nullable ,columns.max_length AS max_length ,columns.precision AS precision ,columns.scale AS scale ,extProp.value AS column_comment FROM sys.tables tables INNER JOIN sys.columns columns ON tables.object_id = columns.object_id /* DEFAULT制約定義を取得する為に以下の二つのテーブルを外部結合 */ LEFT OUTER JOIN sys.sysconstraints constraints ON columns.object_id = constraints.id AND columns.column_id = constraints.colid /* DEFAULT制約を表す疑似ビットマスク値 (マスクしていないと取得できない場合があるとのこと. (thanks murasukeさん) */ AND (constraints.status & 2069) = 2069 LEFT OUTER JOIN sys.default_constraints defaultConstraints ON constraints.constid = defaultConstraints.object_id AND tables.schema_id = defaultConstraints.schema_id /* コメントデータは拡張プロパティシステムビューに存在する */ LEFT OUTER JOIN sys.extended_properties extProp ON /* カラムの場合、classの値は常に1 (OBJECT_OR_COLUMN) */ extProp.class = 1 AND columns.object_id = extProp.major_id AND columns.column_id = extProp.minor_id /* プライマリーキーの情報を取得する為に以下の情報を外部結合 */ LEFT OUTER JOIN ( SELECT key_const.name AS constraint_name ,idx_cols.key_ordinal AS key_ordinal ,cols.name AS col_name ,cols.object_id AS col_object_id ,cols.column_id AS col_column_id FROM sys.tables tbls /* PKの情報を結合 */ INNER JOIN sys.key_constraints key_const ON tbls.object_id = key_const.parent_object_id AND key_const.type = 'PK' /* 対応するインデックス情報からカラムと特定 */ INNER JOIN sys.index_columns idx_cols ON key_const.parent_object_id = idx_cols.object_id AND key_const.unique_index_id = idx_cols.index_id INNER JOIN sys.columns cols ON idx_cols.object_id = cols.object_id AND idx_cols.column_id = cols.column_id ) pk_cols ON columns.object_id = pk_cols.col_object_id AND columns.column_id = pk_cols.col_column_id /* ユニークキーの情報を取得する為に以下の情報を外部結合 */ LEFT OUTER JOIN ( SELECT key_const.name AS constraint_name ,idx_cols.key_ordinal AS key_ordinal ,cols.name AS col_name ,cols.object_id AS col_object_id ,cols.column_id AS col_column_id FROM sys.tables tbls /* UQ(ユニークキー)の情報を結合 */ INNER JOIN sys.key_constraints key_const ON tbls.object_id = key_const.parent_object_id AND key_const.type = 'UQ' /* 対応するインデックス情報からカラムと特定 */ INNER JOIN sys.index_columns idx_cols ON key_const.parent_object_id = idx_cols.object_id AND key_const.unique_index_id = idx_cols.index_id INNER JOIN sys.columns cols ON idx_cols.object_id = cols.object_id AND idx_cols.column_id = cols.column_id ) uq_cols ON columns.object_id = uq_cols.col_object_id AND columns.column_id = uq_cols.col_column_id ORDER BY db_name, schema_name, table_name, columns.column_id GO
INFORMATION_SCHEMAを利用したら上記のデータはいちいちこんなことしなくても
大半が一発で取得できるのですが、INFORMATION_SCHEMAってobject_idがないので
テーブル/カラムの説明や、DEFAULT制約の定義等を取れないんですよね。
やり方をご存知の方、教えてください。m(_ _)m
2011/06/10 追記:
Default制約を表す疑似ビットマスクは、論理積を利用していないと
取得出来ない場合があるとのこと。(murasukeさん、ご指摘ありがとうございます。)
AND (constraints.status & 2069) = 2069
記事内のSQLは修正済み。