SELECT

DB_NAME(DB_ID()) as DatabaseName,

OBJECT_NAME(ddips.[object_id], DB_ID())  AS TableName ,

i.[name] AS [Index_Name] ,

ddips.[Index_Type_Desc] ,

CAST(ddips.[avg_fragmentation_in_percent] AS SMALLINT)AS [Logical_Scan_Fragment_%] ,

CAST(ddips.[avg_fragment_size_in_pages] AS SMALLINT)AS [Avg_Frag_Size_in_Pages] ,

ddips.[Fragment_Count] ,

ddips.[page_count],

ddips.[avg_page_space_used_in_percent] AS Avg_Page_Dens,

ddips.[Partition_Number] ,

ddips.[Alloc_Unit_Type_Desc] ,

ddips.[Index_Depth] ,

ddips.[Index_Level]

FROM

sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, ‘Detailed’) ddips

INNER JOIN sys.[indexes] i ON ddips.[object_id] = i.[object_id]

AND ddips.[index_id] = i.[index_id] WHERE

ddips.[avg_fragmentation_in_percent] > 15

AND ddips.[page_count] > 1000 ORDER BY

ddips.[avg_fragmentation_in_percent] DESC,

OBJECT_NAME(ddips.[object_id], DB_ID()), i.[name]