Index Fragmentation Report in MSSQL Server

In order to get meaningful information about the fragmentation percentage of all indexes under the specified database from the sys.dm_db_index_physical_stats DMF, you need to join it with the sys.indexes DMV, as in the T-SQL script below:

Step 1:  Open MSSMS and click on New Query.

Step 2: Select the database where you want to run the query.

Step 3: Now, copy and paste the below script and Execute the query.

SELECT OBJECT_NAME(Index_Info.OBJECT_ID) AS TableName
  ,Index_Info.name AS IndexName
  ,Index_Stat.index_type_desc AS IndexType
  ,Index_Stat.avg_fragmentation_in_percent IndexFragmPercent
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) Index_Stat
INNER JOIN sys.indexes Index_Info ON Index_Info.object_id = Index_Stat.object_id
  AND Index_Info.index_id = Index_Stat.index_id
ORDER BY IndexFragmPercent DESC

Step 4: The result should looks like the below picture.

Step 5: The IndexFragmPercent column is displaying the percentage of fragmentation of the table. Lower the number is better.