SqlServerでカラムの初期値制約まで含めた情報を取得するSQL。

カラム初期値を取得する「sys.default_constraints.definition」は、object_definition(object_id)関数を利用しており、以下の権限がないとエラーではなくただNULLを返す。忘れると3日ほどハマることになるので注意!!

ユーザー オブジェクトの定義は、オブジェクトの所有者、または ALTER, CONTROL、TAKE OWNERSHIP、VIEW DEFINITION のいずれかの権限を許可された人が表示できます。 これらの権限は db_owner、db_ddladmin、および db_securityadmin 固定データベース ロールのメンバーが暗黙的に保有します。

/* カラム定義情報を取得 */
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