Rebuild Indexes

RebuildIndexes

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET NOCOUNT ON
GO

IF (OBJECT_ID('RebuildIndexes') IS NOT NULL)
  DROP PROCEDURE RebuildIndexes
GO

CREATE procedure [dbo].[RebuildIndexes]
(
@DatabaseName VARCHAR(255),
@ExcludeTables VARCHAR(MAX) -- e.g. = '''AUDIT'',''ARCH_AUDIT'''
)
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'' AND table_name not in (''' + @ExcludeTables + ''') '
			EXEC (@cmd)
			OPEN TableCursor
				FETCH NEXT FROM TableCursor INTO @Table
					WHILE @@FETCH_STATUS = 0
						BEGIN
						print 'Rebuilding ' + @Table
						declare @start datetime = Getdate()
						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 = OFF)'  --ON for enterprise edition with try catch to switch to offline
						EXEC (@cmd)
						print 'Completed ' + @Table + '. Time (secs): ' + CONVERT(VARCHAR(50), DATEDIFF(ss,@start,getdate()))
			FETCH NEXT FROM TableCursor INTO @Table
						END
			CLOSE TableCursor
			DEALLOCATE TableCursor
	FETCH NEXT FROM DatabaseCursor INTO @Database
END
CLOSE DatabaseCursor
DEALLOCATE DatabaseCursor