declare @db int
set @db = db_id()
SELECT object_name(s.object_id), i.name, i.type_desc, index_depth,
avg_fragmentation_in_percent, fragment_count, page_count
FROM sys.dm_db_index_physical_stats (@db, null, NULL, NULL, NULL) s
join sys.indexes i
on i.object_id = s.object_id
And i.index_id = s.index_id
JOIN sys.partitions p
On p.object_id = s.object_id
And p.partition_number = s.partition_number
And p.index_id = s.index_id
where avg_fragmentation_in_percent > 29
--and p.rows > 9999
--and index_depth > 2
Database Fragmentation
Posted - 03/14/2013 : 08:30:26
Glad to help