SQLServerでユニークインデックスとカラム情報を取得してテーブル定義書を作成する
SQLServerで使用されているテーブルの定義書をSQLで簡単に出力できれば便利かと思い、以前に書いた記事のSQLを修正して、ユニークインデックスの情報も出力するように変更してみました。
https://it-engineer-info.com/database/sqlserver/3190/ユニークインデックスとカラム情報を取得する方法
今回は参照記事のsys.objects, sys.columnsというシステムビューに加えsys.tables,sys.key_constraints,sys.index_columnsも合わせて参照するようにします。
実際にSQLServerでユニークインデックスとカラム情報を取得するSQLは以下の通りです。
-----------------------------------------
--テスト用のターゲットテーブル生成
-----------------------------------------
CREATE TABLE テストテーブル
(
プライマリキー１ varchar(50) NOT NULL
,プライマリキー２ smallint NOT NULL
,カラム１ varchar(50)
,カラム２ smallint NOT NULL
,CONSTRAINT [PK_テストテーブル] PRIMARY KEY CLUSTERED
(
プライマリキー１ ASC
,プライマリキー２ 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許可 プライマリキー テストテーブル プライマリキー１ varchar 50 NO 1 テストテーブル プライマリキー２ smallint 2 NO 2 テストテーブル カラム１ varchar 50 YES テストテーブル カラム２ smallint 2 NO
のような結果が得られます。
これで「テーブル名」「項目名」「属性」「長さ」「NULLの許可」「プライマリキー」が取得できました。
簡易的なテーブル定義書の下書きがこれで完成です。
以上、SQLServerでユニークインデックスとカラム情報を取得してテーブル定義書を作成するでした。