いろいろ備忘録日記

主に .NET とか Go とか Flutter とか Python絡みのメモを公開しています。

テーブルとビューの定義情報を取得 (sys.tables, sys.columns, sys.objects, sys.extend_properties, sys.constraints, sys.default_constraints)


前に、テーブルとカラムの説明を取得する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は修正済み。