The Wafflings of Nullh

Checking Index Fragmentation [CODE WARNING]

I’m constantly peeved by how annoying it is to get the fragmentation of SQL Server indexes with something as obvious as the index names included. There doesn’t seem to be the code out there for this, and I’m sick of having to rewrite JOINS for it every time, so I’m dumping this here, where I’ll find it again.

declare @dbid int
declare @tableid int
— Replace the database name here:
set @dbid = (select db_id(‘[database]’))
— Replace the table name here:
set @tableid = (select object_id(‘[schema].[table]’, ‘table’))


select s2.name, index_type_desc, s1.index_id, avg_fragmentation_in_percent, fragment_count,
avg_fragment_size_in_pages, page_count
from sys.dm_db_index_physical_stats (@dbid, @tableid, NULL, NULL, DEFAULT) s1
cross apply
(select name, index_id from sys.indexes
where s1.object_id = object_id and s1.index_id = index_id) as s2
order by avg_fragmentation_in_percent desc