Bir Tabloya Ait İndekslerin İstatistiklerini Bulma

SELECT
object_name(sys.indexes.Object_id) AS Table_Name,
ISNULL(sys.indexes.name, '---') AS index_name,
partitions.Rows,
partitions.SizeMB,
sys.indexes.type_desc,
sys.indexes.is_unique,
sys.indexes.is_primary_key,
sys.indexes.is_unique_constraint,
sys.indexes.is_unique,
ISNULL(Index_Columns.index_columns_key, '---') AS index_columns_key,
ISNULL(Index_Columns.index_columns_include, '---') AS index_columns_include,
ISNULL(sys.dm_db_index_usage_stats.user_seeks,0) AS user_seeks,
ISNULL(sys.dm_db_index_usage_stats.system_seeks,0) AS system_seeks,
ISNULL(sys.dm_db_index_usage_stats.user_scans,0) AS user_scans,
ISNULL(sys.dm_db_index_usage_stats.system_scans,0) AS system_scans,
ISNULL(sys.dm_db_index_usage_stats.user_lookups,0) AS user_lookups,
ISNULL(sys.dm_db_index_usage_stats.system_lookups,0) AS system_lookups,
ISNULL(sys.dm_db_index_usage_stats.user_updates,0) AS user_updates,
ISNULL(sys.dm_db_index_usage_stats.system_updates,0) AS system_updates,
(
(
(CONVERT(Numeric(19,6), ISNULL(sys.dm_db_index_usage_stats.user_seeks,0))+CONVERT(Numeric(19,6), ISNULL(sys.dm_db_index_usage_stats.system_seeks,0)))*10
+ CASE WHEN sys.indexes.type=2 THEN (CONVERT(Numeric(19,6), ISNULL(sys.dm_db_index_usage_stats.user_scans,0))+CONVERT(Numeric(19,6), ISNULL(sys.dm_db_index_usage_stats.system_scans,0)))*1 ELSE 0 END
+ 1
)
/CASE WHEN sys.indexes.type=2 THEN (CONVERT(Numeric(19,6), ISNULL(sys.dm_db_index_usage_stats.user_updates,0))+CONVERT(Numeric(19,6), ISNULL(sys.dm_db_index_usage_stats.system_updates,0))+1) ELSE 1 END
) AS Score
FROM
sys.indexes
JOIN (
SELECT
object_id, index_id, SUM(row_count) AS Rows,
CONVERT(numeric(19,3), CONVERT(numeric(19,3), SUM(in_row_reserved_page_count+lob_reserved_page_count+row_overflow_reserved_page_count))/CONVERT(numeric(19,3), 128)) AS SizeMB
FROM sys.dm_db_partition_stats
GROUP BY object_id, index_id
) AS partitions ON sys.indexes.object_id=partitions.object_id AND sys.indexes.index_id=partitions.index_id
CROSS APPLY (
SELECT
LEFT(index_columns_key, LEN(index_columns_key)-1) AS index_columns_key,
LEFT(index_columns_include, LEN(index_columns_include)-1) AS index_columns_include
FROM
(
SELECT
(
SELECT sys.columns.name + ', '
FROM
sys.index_columns
JOIN sys.columns ON
sys.index_columns.column_id=sys.columns.column_id
AND sys.index_columns.object_id=sys.columns.object_id
WHERE
sys.index_columns.is_included_column=0
AND sys.indexes.object_id=sys.index_columns.object_id AND sys.indexes.index_id=sys.index_columns.index_id
ORDER BY key_ordinal
FOR XML PATH('')
) AS index_columns_key,
(
SELECT sys.columns.name + ', '
FROM
sys.index_columns
JOIN sys.columns ON
sys.index_columns.column_id=sys.columns.column_id
AND sys.index_columns.object_id=sys.columns.object_id
WHERE
sys.index_columns.is_included_column=1
AND sys.indexes.object_id=sys.index_columns.object_id AND sys.indexes.index_id=sys.index_columns.index_id
ORDER BY index_column_id
FOR XML PATH('')
) AS index_columns_include
) AS Index_Columns
) AS Index_Columns
LEFT OUTER JOIN sys.dm_db_index_usage_stats ON
sys.indexes.index_id=sys.dm_db_index_usage_stats.index_id AND sys.indexes.object_id=sys.dm_db_index_usage_stats.object_id
AND sys.dm_db_index_usage_stats.database_id=DB_ID()
WHERE
sys.indexes.object_id=object_id('TableName')