MSSQL: Find tables that need indexing then generate rebuild/reorganise script

I’ve slightly modified this, but I can’t remember exactly where it came from so I cannot take all the credit.

This script run against the specified database, will identify tables that are too fragmented and generate scripts to that you can copy and past to fix. Try it out!

use DatabaseNameGoesHere



'ALTER INDEX [' + + '] ON [' + + '].[' + + '] ' +

CASE WHEN ps.avg_fragmentation_in_percent > 40 THEN 'REBUILD' ELSE 'REORGANIZE' END +

CASE WHEN pc.partition_count > 1 THEN ' PARTITION = ' + cast(ps.partition_number as nvarchar(max)) ELSE '' END as IndexScript


sys.indexes AS ix

INNER JOIN sys.tables t ON t.object_id = ix.object_id

INNER JOIN sys.schemas s ON t.schema_id = s.schema_id

INNER JOIN (SELECT object_id, index_id, avg_fragmentation_in_percent, partition_number

FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL)) ps ON t.object_id = ps.object_id AND ix.index_id = ps.index_id

INNER JOIN (SELECT object_id, index_id, COUNT(DISTINCT partition_number) AS partition_count

FROM sys.partitions

GROUP BY object_id, index_id) pc ON t.object_id = pc.object_id AND ix.index_id = pc.index_id


ps.avg_fragmentation_in_percent > 10


ORDER BY avg_fragmentation_in_percent DESC


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