いろいろ備忘録日記

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

プライマリーキー(PK)とユニークキー(UQ)の定義情報を取得 (sys.key_constraints, sys.index_columns, sys.tables, sys.columns)


プライマリーキーとユニークキーの定義情報の取得方法です。
忘れないうちにメモメモ。ついでに、先日のカラム定義を取得するSQLにも
組み込んでおきました。


[プライマリーキー情報]

SELECT
     tbls.name            AS table_name
    ,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
        /* 制約定義情報を結合 */
        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
ORDER BY
    tbls.object_id, idx_cols.key_ordinal, idx_cols.column_id


[ユニークキー情報]

SELECT
     tbls.name            AS table_name
    ,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
        /* 制約定義情報を結合 */
        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
ORDER BY
    tbls.object_id, idx_cols.key_ordinal, idx_cols.column_id