Hi,

Below is a script to rebuild indexes, it will try online operations, which can fail, in the event of a failure it will switch to offline mode.

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET NOCOUNT ON

GO

CREATE procedure [dbo].[RebuildIndexes]

(

@DatabaseName VARCHAR(255)

)

as

DECLARE @Database VARCHAR(255)

DECLARE @Table VARCHAR(255)

DECLARE @cmd NVARCHAR(500)

DECLARE @fillfactor INT

SET @fillfactor = 75

DECLARE DatabaseCursor CURSOR FOR

SELECT name FROM master.dbo.sysdatabases

WHERE name IN (@DatabaseName)

ORDER BY 1

OPEN DatabaseCursor

FETCH NEXT FROM DatabaseCursor INTO @Database

WHILE @@FETCH_STATUS = 0

BEGIN

SET @cmd = ‘DECLARE TableCursor CURSOR FOR SELECT table_catalog + ”.” + table_schema + ”.” + table_name as tableName

FROM ‘ + @Database + ‘.INFORMATION_SCHEMA.TABLES WHERE table_type = ”BASE TABLE”’

— create table cursor

EXEC (@cmd)

OPEN TableCursor

FETCH NEXT FROM TableCursor INTO @Table

WHILE @@FETCH_STATUS = 0

BEGIN

— SQL 2000 command

–DBCC DBREINDEX(@Table,’ ‘,@fillfactor)

— SQL 2005 command

print ‘Rebuilding ‘ + @Table

Select @Table = Substring(@table, 0, LEN(@table) – PATINDEX(‘%.%’, REVERSE(@table)) + 2) + ‘[‘ +

SUBSTRING(@table, LEN(@table) – PATINDEX(‘%.%’, REVERSE(@table)) + 2, LEN(@table)) + ‘]’

SET @cmd = ‘ALTER INDEX ALL ON ‘ + @Table + ‘ REBUILD WITH (FILLFACTOR = ‘ + CONVERT(VARCHAR(3),@fillfactor) + ‘, ONLINE = ON)’

BEGIN TRY

EXEC (@cmd)

END TRY

BEGIN CATCH

print ‘Table: ‘ + @table + ‘ has been skipped as ONLINE, switching to OFFLINE’

SELECT

ERROR_NUMBER() AS ErrorNumber

,ERROR_SEVERITY() AS ErrorSeverity

,ERROR_STATE() AS ErrorState

,ERROR_PROCEDURE() AS ErrorProcedure

,ERROR_LINE() AS ErrorLine

,ERROR_MESSAGE() AS ErrorMessage;

SET @cmd = ‘ALTER INDEX ALL ON ‘ + @Table + ‘ REBUILD WITH (FILLFACTOR = ‘ + CONVERT(VARCHAR(3),@fillfactor) + ‘, ONLINE = OFF)’

EXEC (@cmd)

END CATCH

FETCH NEXT FROM TableCursor INTO @Table

END

CLOSE TableCursor

DEALLOCATE TableCursor

FETCH NEXT FROM DatabaseCursor INTO @Database

END

CLOSE DatabaseCursor

DEALLOCATE DatabaseCursor

• Uncategorized