So today I wanted to see if I could make a query that would hash a database to see how fragmented index’s were in the SQL Management Studio.

The below script basically queries the entire database and sees where the problem children exist. You would just need to insert your database name in the field in the ‘Use’ for you to use the query to your needs.

Enjoy!!!!

USE [Insert Database Name here]
SELECT OBJECT_NAME(ind.OBJECT_ID) AS TableName
,ind.name AS IndexName
,indexstats.index_type_desc AS IndexType
,indexstats.avg_fragmentation_in_percent
,page_count AS page_count
FROM sys.dm_db_index_physical_stats(DB_ID() ,NULL ,NULL ,NULL ,NULL) indexstats
INNER JOIN sys.indexes ind
ON ind.object_id = indexstats.object_id
AND ind.index_id = indexstats.index_id
WHERE indexstats.avg_fragmentation_in_percent > 20
AND page_count > 50
AND indexstats.index_type_desc <> ‘Heap’
ORDER BY indexstats.avg_fragmentation_in_percent DESC