SQLサーバの全テーブルに貼られているインデックス一覧を対象カラムとともに取得する

●SQLサーバのインデックス一覧を取得する

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;

引用: https://bit.ly/3c5izfS

No.1749
05/01 13:54

edit