Reply to comment

Get Index Fragmentation Percentage for all Tables in SQL Server Database

Submitted by Ben Hodson on Sun, 10/19/2008 - 15:17

Here is a useful script I've been able to put together from multiple posts online that gets the estimated index fragmentation for all tables in your database where the fragmentation percentage is greater than the where clause. Here's the SQL:

select object_name(itable.object_id) as tablename,
itable.name as IndexName,
indexfrag.avg_fragmentation_in_percent
from sys.dm_db_index_physical_stats(db_id(), null, null, null, 'DETAILED') indexfrag
inner join sys.indexes itable on itable.object_id = indexfrag.object_id
and itable.index_id = indexfrag.index_id
-- make sure to set this where clause to the percentage below which you want to exclude results.
where indexfrag.avg_fragmentation_in_percent > 20
order by avg_fragmentation_in_percent desc, tablename



Reply