Get Index Fragmentation Percentage For All Tables In SQL Server Database

select object_name(itable.object_id) as tablename, as IndexName,
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


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s