> >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 > >