Index Fragmentation Status Script

This script will let you know “how much your index is fragmented”

 

SELECT

             OBJECT_NAME(o.object_id) AS TableName

            ,i.name  AS IndexName

            ,i.type_desc AS IndexType

            ,CASE WHEN ISNULL(ps.function_id,1) = 1 THEN 'NO' ELSE 'YES' END AS Partitioned

            ,COALESCE(fg.name ,fg2.name) AS FileGroupName

            ,p.partition_number AS PartitionNumber

            ,p.rows AS PartitionRows

            ,ddips.Avg_Fragmentation_In_Percent

            ,ddips.Fragment_Count

            ,ddips.Avg_Fragment_Size_In_Pages

            ,ddips.Page_Count

            ,prvl.value  AS PartitionLowerBoundaryValue

            ,prvr.value AS PartitionUpperBoundaryValue

            ,CASE WHEN pf.boundary_value_on_right = 1 THEN 'RIGHT' WHEN pf.boundary_value_on_right = 0 THEN 'LEFT' ELSE 'NONE' END AS PartitionRange

            ,pf.name        AS PartitionFunction

            ,ds.name AS PartitionScheme

FROM sys.partitions AS p 

INNER JOIN sys.indexes AS i 

            ON i.object_id = p.object_id

            AND i.index_id = p.index_id

INNER JOIN sys.objects AS o 

            ON o.object_id = i.object_id

INNER JOIN sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, N'LIMITED') ddips

            ON ddips.OBJECT_ID = i.object_id

            AND ddips.index_id = i.index_id

            AND ddips.partition_number  = p.partition_number

LEFT JOIN sys.data_spaces AS ds 

      ON ds.data_space_id = i.data_space_id

LEFT JOIN sys.partition_schemes AS ps 

      ON ps.data_space_id = ds.data_space_id

LEFT JOIN sys.partition_functions AS pf 

      ON pf.function_id = ps.function_id

LEFT JOIN sys.destination_data_spaces AS dds 

      ON dds.partition_scheme_id = ps.data_space_id

      AND dds.destination_id = p.partition_number

LEFT JOIN sys.filegroups AS fg 

      ON fg.data_space_id = i.data_space_id

LEFT JOIN sys.filegroups AS fg2 

      ON fg2.data_space_id = dds.data_space_id

LEFT JOIN sys.partition_range_values AS prvl 

      ON ps.function_id = prvl.function_id

      AND prvl.boundary_id = p.partition_number - 1

LEFT JOIN sys.partition_range_values AS prvr 

      ON ps.function_id = prvr.function_id

      AND prvr.boundary_id = p.partition_number

ORDER BY

            SchemaName

    ,TableName

    ,IndexName

    ,PartitionNumber