SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE [dbo].[sDefragmentIndices] AS SET NOCOUNT ON DECLARE @objectid int DECLARE @indexid int DECLARE @command varchar(8000) DECLARE @baseCommand varchar(8000) DECLARE @schemaname sysname DECLARE @objectname sysname DECLARE @indexname sysname DECLARE @currentDdbId int SELECT @currentDdbId = DB_ID() PRINT CONVERT(nvarchar, GETDATE(), 126) + ': Starting' -- Loop over each of the indices DECLARE indexesToDefrag CURSOR FOR SELECT i.object_id, i.index_id, i.name FROM sys.indexes AS i INNER JOIN sys.objects AS o ON i.object_id = o.object_id WHERE i.index_id > 0 AND o.type = 'U' OPEN indexesToDefrag FETCH NEXT FROM indexesToDefrag -- Loop through the partitions. INTO @objectid, @indexid, @indexname WHILE @@FETCH_STATUS = 0 BEGIN -- Lookup the name of the index SELECT @schemaname = s.name FROM sys.objects AS o JOIN sys.schemas AS s ON s.schema_id = o.schema_id WHERE o.object_id = @objectid PRINT CONVERT(nvarchar, GETDATE(), 126) + ': ' + @schemaname + '.' + @indexname + ' is now being rebuilt.' -- Fragmentation is bad enough that it will be more efficient to rebuild the index SELECT @baseCommand = ' ALTER INDEX ' + @indexname + ' ON ' + @schemaname + '.' + object_name(@objectid) + ' REBUILD WITH (FILLFACTOR = 80, ONLINE = ' -- Use dynamic sql so this compiles in SQL 2000 SELECT @command = ' BEGIN TRY ' + @baseCommand + 'ON) ' + ' END TRY ' + ' BEGIN CATCH ' + @baseCommand + 'OFF) ' + -- Indices with image-like columns can't be rebuild online, so go offline ' END CATCH ' PRINT CONVERT(nvarchar, GETDATE(), 126) + ': Rebuilding' EXEC (@command) PRINT CONVERT(nvarchar, GETDATE(), 126) + ': Done' FETCH NEXT FROM indexesToDefrag INTO @objectid, @indexid, @indexname END CLOSE indexesToDefrag DEALLOCATE indexesToDefrag RETURN 0