今天分享的脚本功能是：
返回当前db下所有索引的状态，包括表名、索引名、索引类型、索引字段、包含列的索引字段、填充因子、索引行数、使用的页面数量、索引碎片程度等，几乎涵盖了索引的方方面面；
--First, please change thedatabase context to the specified database.
USE ABCD
GO
SELECT
db_name()AS [Database],
schema_name(o.schema_id) AS [Schema],
o.name AS [Table],
o.type_desc AS Table_Type,
i.index_id,
ISNULL(i.name,'') AS Index_Name,
i.type_desc AS Index_Type,
ISNULL([KeyColumns],'') AS Index_Keys,
ISNULL([IncludeColumns],'') AS Index_Included_Column,
CASE i.is_disabled WHEN 1 THEN 'Yes' ELSE '' END AS is_Disabled,
CASE i.is_unique WHEN 1 THEN 'Yes' ELSE '' END AS is_Unique,
CASE i.is_primary_key WHEN 1 THEN 'Yes' ELSE '' END AS is_Primary_Key,
i.fill_factor,
ISNULL([RowCount],'') AS Row_Count,
UsedPages,
ReservedPages,
dm.partition_number AS partitionnum,
dm.avg_fragmentation_in_percent ASfrag
FROM sys.objects o
INNER JOIN sys.indexesi ON o.object_id = i.object_id
INNER JOIN sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL , NULL, 'LIMITED') dm ON dm.object_id = o.object_id AND dm.index_id = i.index_id
OUTER APPLY(
SELECT [KeyColumns]= stuff(replace(replace(
(
SELECTc.name + CASE WHEN is_descending_key = 1THEN ' desc' ELSE '' END AS [ColumnName]
FROMsys.index_columns ic JOIN sys.columns c ON ic.object_id = c.object_id AND ic.column_id = c.column_id
WHEREic.is_included_column =0 AND ic.object_id = i.object_id AND ic.index_id = i.index_id
ORDERBY ic.index_column_id
FORXML AUTO
), '<icColumnName="', ','), '"/>', ''), 1, 1, '')
) AS T_KeyCol
OUTER APPLY(
SELECT [IncludeColumns]= stuff(replace(replace(
(
SELECTc.name AS [ColumnName]
FROMsys.index_columns ic JOIN sys.columns c ON ic.object_id = c.object_id AND ic.column_id = c.column_id
WHEREic.is_included_column =1 AND ic.object_id = i.object_id AND ic.index_id = i.index_id
ORDERBY ic.index_column_id
FORXML AUTO
), '<cColumnName="', ','), '"/>', ''), 1, 1, '')
) AS T_IncCol
OUTER APPLY(
SELECT [RowCount]= SUM(row_count), [UsedPages] = SUM(used_page_count), [ReservedPages] = SUM(reserved_page_count)
FROM sys.dm_db_partition_statsddps
WHERE ddps.object_id = i.object_id AND ddps.index_id = i.index_id
) AS T_Size
WHERE o.type_desc IN('USER_TABLE','VIEW')
ORDER BY 1,2,4,3,5
还不拷走try try try？
