SQL Serverでインデックス定義を列名はカンマ区切りで抽出する

SQL Serverでインデックス定義を見やすく抽出する
http://d.hatena.ne.jp/qaz76/20110513/1305250822
はどちらかというと分析用。

こちらは純粋かつコンパクトに定義情報を取り出す用。
ポイントは、"FOR XML PATH"で縦->横変換しているところです。

方法

このSQLを実行します。

WITH
	CTE1
AS
(
SELECT
	T4.NAME TABLE_NAME,
	T1.NAME INDEX_NAME,
	T1.IS_PRIMARY_KEY,
	T1.TYPE_DESC,
	T2.KEY_ORDINAL,
	T1.OBJECT_ID,
	T1.INDEX_ID,
	T3.COLUMN_ID,
	T3.NAME COLUMN_NAME
FROM
	SYS.INDEXES T1
INNER JOIN
	SYS.INDEX_COLUMNS T2
ON
	T1.OBJECT_ID = T2.OBJECT_ID
	AND
	T1.INDEX_ID = T2.INDEX_ID
INNER JOIN
	SYS.COLUMNS T3
ON
	T2.OBJECT_ID = T3.OBJECT_ID
	AND
	T2.COLUMN_ID = T3.COLUMN_ID
INNER JOIN
	SYS.TABLES T4
ON
	T1.OBJECT_ID = T4.OBJECT_ID
WHERE
	T4.SCHEMA_ID = SCHEMA_ID('dbo')
),
	CTE2
AS
(
SELECT
	TABLE_NAME,
	OBJECT_ID,
	index_id,
	INDEX_NAME,
	IS_PRIMARY_KEY,
	TYPE_DESC,
	COUNT(*) COLUMNS
FROM
	CTE1
GROUP BY
	TABLE_NAME,
	OBJECT_ID,
	index_id,
	INDEX_NAME,
	IS_PRIMARY_KEY,
	TYPE_DESC
)
SELECT
	TABLE_NAME,
	INDEX_NAME,
	IS_PRIMARY_KEY,
	TYPE_DESC,
	COLUMNS,
	(
	SELECT
		CASE WHEN KEY_ORDINAL = 1 THEN '' ELSE ', ' END + T2.COLUMN_NAME
	FROM
		CTE1 AS T2
	WHERE
		T1.OBJECT_ID = T2.OBJECT_ID
		AND
		T1.INDEX_ID = T2.INDEX_ID
	ORDER BY
		KEY_ORDINAL
	FOR XML PATH('')
	) COLUMN_NAMES
FROM
	CTE2 AS T1
ORDER BY
	TABLE_NAME,
	INDEX_NAME
;

抽出例

Work! Enjoy it!