SQL Server: データベース内の全てのインデックス (index) を取得する

データベース内の全てのインデックス (index) を取得するには？

インデックスの見直しなどで、データベース内の全インデックス (index) の一覧を確認したいような時があるかもしれません。

今回は、データベース内の全てのインデックス (index) を取得するサンプルクエリーをご紹介します。

sys.indexes を使って全インデックス (index) を取得する

sys.indexes というシステムカタログビューを使うと、選択されているデータベース内のインデックスの情報を取得することができます。

データベース内のユーザーが定義したオブジェクトに生成した全てのインデックス (index) を取得するクエリーは以下の通りです。

SELECT	S.name AS SchemaName,
	O.name AS ObjectName,
	I.name AS IndexName,
	I.type_desc AS IndexTypeDesc,
	I.is_primary_key AS IsPrimaryKey,
	I.is_unique AS IsUnique,
	I.is_disabled AS IsDisabled
FROM	sys.indexes AS I
	   INNER JOIN sys.objects AS O
	      ON I.object_id = O.object_id
	   INNER JOIN sys.schemas AS S
	      ON O.schema_id = S.schema_id
WHERE	I.index_id > 0
	AND O.is_ms_shipped = 0
ORDER BY S.name,
	 O.name,
	 I.name;

SchemaName と ObjectName はそのインデックスが属しているテーブルやビューのものです。

sys.indexes の index_id > 0 でフィルターすることでヒープのオブジェクトを除いて、クラスター化と非クラスター化インデックスを取得しています。

システムカタログビューの sys.objects の is_ms_shipped = 0 でフィルターすることで、ユーザーが定義したオブジェクトに生成したインデックスを取得しています。

インデックスのキー列と非キー列（付加列）も同時に取得する

インデックスのキー列と非キー列（付加列）も同時に確認したい時があるかもしれません。

そんな時は、システムカタログビューの sys.index_columns から情報を取得できます。

インデックスの一覧と、そのキー列を KeyColumns に、非キー列を IncludedColumns として、カンマ区切りで取得するクエリーは以下の通りです。

SELECT	S.name AS SchemaName,
	O.name AS ObjectName,
	I.name AS IndexName,
	I.type_desc AS IndexTypeDesc,
	I.is_primary_key AS IsPrimaryKey,
	I.is_unique AS IsUnique,
	I.is_disabled AS IsDisabled,
	STUFF((SELECT	',' + COL_NAME(IC.object_id, IC.column_id)
               FROM	sys.index_columns AS IC
	       WHERE	IC.is_included_column = 0
			AND IC.object_id = I.object_id
			AND IC.index_id = I.index_id
               ORDER BY IC.key_ordinal
               FOR XML PATH ('')), 1, 1, '') AS KeyColumns,
	STUFF((SELECT	',' + COL_NAME(IC.object_id, IC.column_id)
               FROM	sys.index_columns AS IC
	       WHERE	IC.is_included_column = 1
			AND IC.object_id = I.object_id
			AND IC.index_id = I.index_id
               ORDER BY IC.index_column_id
               FOR XML PATH ('')), 1, 1, '') AS IncludedColumns
FROM	sys.indexes AS I
	   INNER JOIN sys.objects AS O
	      ON I.object_id = O.object_id
	   INNER JOIN sys.schemas AS S
	      ON O.schema_id = S.schema_id
WHERE	I.index_id > 0
	AND O.is_ms_shipped = 0
ORDER BY S.name,
	 O.name,
	 I.name;

sys.index_columns の is_included_column が 0 のレコードがキー列、 1 のレコードが非キー列です。

インデックスの断片化情報を取得する

おまけに sys.dm_db_index_physical_stats というシステム動的管理ビューを使うと、インデックスの断片化などの情報を取得することができます。

SELECT	S.name AS SchemaName,
	O.name AS ObjectName,
	I.name AS IndexName,	
	PS.avg_fragmentation_in_percent
FROM	sys.indexes AS I
	   INNER JOIN sys.objects AS O
	　　　   ON I.object_id = O.object_id
	   INNER JOIN sys.schemas AS S
	　　　   ON O.schema_id = S.schema_id
	   INNER JOIN sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, NULL) AS PS
	　　　   ON I.object_id = PS.object_id
	         AND I.index_id = PS.index_id
WHERE	I.index_id > 0
	AND O.is_ms_shipped = 0
	AND PS.avg_fragmentation_in_percent > 0
ORDER BY　S.name,
	　O.name,
	　I.name;

最後のクエリーはデータベース内の全インデックスではなく、avg_fragmentation_in_percent が 0 より大きいインデックスのみを取得していますのでご注意ください。