SQLServerでユニークインデックスとカラム情報を取得してテーブル定義書を作成する

2019-10-24SQLServer

SQLServerで使用されているテーブルの定義書をSQLで簡単に出力できれば便利かと思い、以前に書いた記事のSQLを修正して、ユニークインデックスの情報も出力するように変更してみました。

ユニークインデックスとカラム情報を取得する方法

今回は参照記事のsys.objects, sys.columnsというシステムビューに加えsys.tables,sys.key_constraints,sys.index_columnsも合わせて参照するようにします。

実際にSQLServerでユニークインデックスとカラム情報を取得するSQLは以下の通りです。

-----------------------------------------
--テスト用のターゲットテーブル生成
-----------------------------------------
CREATE TABLE テストテーブル
(
     プライマリキー1 varchar(50) NOT NULL
    ,プライマリキー2 smallint    NOT NULL
    ,カラム1         varchar(50)
    ,カラム2         smallint    NOT NULL
,CONSTRAINT [PK_テストテーブル] PRIMARY KEY CLUSTERED 
    (
         プライマリキー1 ASC
        ,プライマリキー2 ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
         IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

-----------------------------------------
--変数定義
-----------------------------------------
DECLARE @table_name  VARCHAR(50) = 'テストテーブル'    --ターゲットになるテーブル名

-----------------------------------------
--テーブル仕様書もどき取得
-----------------------------------------
SELECT
     t.name                                                AS テーブル名
    ,c.name                                                AS 項目名
    ,type_name(c.user_type_id)                             AS 属性
    ,c.max_length                                          AS 長さ
    ,CASE WHEN c.is_nullable = 1 THEN 'YES' ELSE 'NO' END  AS NULL許可
	,ISNULL(CAST(pk.key_ordinal AS VARCHAR),'')            AS プライマリキー
FROM
    sys.objects t
    INNER JOIN sys.columns c ON
        t.object_id = c.object_id
    --以下はプライマリキーの情報を取得
    LEFT OUTER JOIN (
        SELECT
             idx_cols.key_ordinal AS key_ordinal
            ,cols.name            AS col_name
        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'
                AND tbls.name=@table_name
            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
        ) AS pk ON
        c.name = pk.col_name
    --ここまでプライマリキーの情報を取得
WHERE
        t.type = 'U'
    AND t.name = @table_name
ORDER BY
    c.column_id

-----------------------------------------
--テスト用のターゲットテーブル削除
-----------------------------------------
DROP TABLE テストテーブル

上記のSQLを実行すると

テーブル名         項目名          属性     長さ NULL許可  プライマリキー
テストテーブル  プライマリキー1  varchar    50    NO           1
テストテーブル  プライマリキー2  smallint    2    NO           2
テストテーブル  カラム1          varchar    50   YES
テストテーブル  カラム2          smallint    2    NO

のような結果が得られます。

これで「テーブル名」「項目名」「属性」「長さ」「NULLの許可」「プライマリキー」が取得できました。

簡易的なテーブル定義書の下書きがこれで完成です。

以上、SQLServerでユニークインデックスとカラム情報を取得してテーブル定義書を作成するでした。