12/10/2013 5:48:18 PM

Rebuild, recreate, or reindex indexes on SQL Server tables that have fragmentation.

DECLARE @TableName varchar(200), @IndexName varchar(200), @SqlCommand nvarchar(500) DECLARE TablesAndIndexes CURSOR FOR SELECT Tables.Name as TableName, Indexes.Name as IndexName FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, 'LIMITED') AS Stats inner join sys.indexes as Indexes on Stats.Index_id = Indexes.Index_ID and Stats.object_ID = Indexes.object_ID inner join sys.objects as Tables on Indexes.Object_ID = Tables.Object_ID WHERE Indexes.Name IS NOT NULL AND avg_fragmentation_in_percent > 10.0 AND Stats.Index_id > 0 AND Page_Count > 300 OPEN TablesAndIndexes FETCH TablesAndIndexes INTO @TableName, @IndexName -- start the main processing loop. WHILE @@Fetch_Status = 0 BEGIN --Print Print('Rebuilding: ' + @IndexName + ' ON ' + @TableName) --Rebuild SET @SqlCommand = 'ALTER INDEX ' + @IndexName + ' ON [' + @TableName + '] REBUILD' EXEC (@SqlCommand) -- Get the next row. FETCH TablesAndIndexes INTO @TableName, @IndexName END CLOSE TablesAndIndexes DEALLOCATE TablesAndIndexes