Get Index Fragmentation Percentage For All Tables In SQL Server Database

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

Advertisements

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s