SQL Tips #25 - Rebuilding All Fragmented Table Indexes

This script finds every index with more than 25% fragmentation and reorganizes it.

The Query

declare @t varchar(250)
declare @i varchar(250)
declare CustList cursor for
    
SELECT OBJECT_NAME(IDX.OBJECT_ID) AS Table_Name, IDX.name AS Index_Name 
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) IDXPS 
INNER JOIN sys.indexes IDX  ON IDX.object_id = IDXPS.object_id 
    AND IDX.index_id = IDXPS.index_id 
    AND IDXPS.avg_fragmentation_in_percent>25

OPEN CustList
FETCH NEXT FROM CustList INTO @t, @i
WHILE @@FETCH_STATUS = 0
BEGIN
    print @t + ':' + @i

    exec('ALTER INDEX ' + @i + ' ON ' + @t +' REORGANIZE  ')
    print 'ALTER INDEX ' + @i + ' ON ' + @t +' REORGANIZE '      

    FETCH NEXT FROM CustList INTO @t, @i
END
CLOSE CustList
DEALLOCATE CustList

Disclaimer

These queries are provided as a guide, and are by no means perfect. I didn't write all of these - many are collated from MSDN documentation and community sources over the years.

Tags