-- Run agaist DB you want to retrieve Index Usage Stats with the fragmentation level
-- Last update by : MsMiller 12-10-10
DECLARE @db_name nvarchar(50);
SET @db_name = db_name();
SELECT o.name Object_Name,
i.name Index_name,
i.Type_Desc,
s.user_seeks,
s.user_scans,
s.user_lookups,
s.user_updates,
phystat.avg_fragmentation_in_percent,
phystat.page_count
FROM sys.objects o, sys.dm_db_index_physical_stats(DB_ID(N''+@db_name+''), NULL, NULL, NULL, NULL) phystat,
sys.indexes i, sys.dm_db_index_usage_stats s
where i.object_id = phystat.object_id AND
i.index_id = phystat.index_id and
o.object_id = i.object_id and
i.object_id = s.object_id AND
i.index_id = s.index_id
AND
i.type IN (1, 2)
order by page_count desc
