--获取索引定义
SELECT index_name
	,index_description
	,(
		LEFT(ind_col, LEN(ind_col) - 1) + CASE 
			WHEN include_col IS NOT NULL
				THEN ' INCLUDE (' + LEFT(include_col, LEN(include_col) - 1) + ')'
			ELSE ''
			END
		) AS index_keys
FROM (
	SELECT i.NAME AS index_name
		,(
			SELECT CONVERT(VARCHAR(max), CASE 
						WHEN i.index_id = 1
							THEN 'clustered'
						ELSE 'nonclustered'
						END + CASE 
						WHEN i.ignore_dup_key <> 0
							THEN ', ignore duplicate keys'
						ELSE ''
						END + CASE 
						WHEN i.is_unique <> 0
							THEN ', unique'
						ELSE ''
						END + CASE 
						WHEN i.is_hypothetical <> 0
							THEN ', hypothetical'
						ELSE ''
						END + CASE 
						WHEN i.is_primary_key <> 0
							THEN ', primary key'
						ELSE ''
						END + CASE 
						WHEN i.is_unique_constraint <> 0
							THEN ', unique key'
						ELSE ''
						END + CASE 
						WHEN s.auto_created <> 0
							THEN ', auto create'
						ELSE ''
						END + CASE 
						WHEN s.no_recompute <> 0
							THEN ', stats no recompute'
						ELSE ''
						END + ' located on ' + ISNULL(NAME, '') + CASE 
						WHEN i.has_filter = 1
							THEN ', filter={' + i.filter_definition + '}'
						ELSE ''
						END)
			FROM sys.data_spaces
			WHERE data_space_id = i.data_space_id
			) AS 'index_description'
		,(
			SELECT INDEX_COL(OBJECT_NAME(i.object_id), i.index_id, key_ordinal)
				,CASE 
					WHEN is_descending_key = 1
						THEN N'(-)'
					ELSE N''
					END + ','
			FROM sys.index_columns
			WHERE object_id = i.object_id
				AND index_id = i.index_id
				AND key_ordinal <> 0
			ORDER BY key_ordinal
			FOR XML PATH('')
			) AS ind_col
		,(
			SELECT col.NAME + ','
			FROM sys.index_columns inxc
			JOIN sys.columns col ON col.object_id = inxc.object_id
				AND col.column_id = inxc.column_id
			WHERE inxc.object_id = i.object_id
				AND inxc.index_id = i.index_id
				AND inxc.is_included_column = 1
			FOR XML PATH('')
			) AS include_col
	FROM sys.indexes i
	JOIN sys.stats s ON i.object_id = s.object_id
		AND i.index_id = s.stats_id
	WHERE i.object_id = object_id('表名')--如果不指定表名，则全库获取
	) Ind
ORDER BY index_name