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