Alter Index All Tables

This script comes in handy when needing to alter all indexes in a database and rebuild them. This will only work on SQL Server 2005+. It utilizes the ALL keyword in the Alter index statement to rebuild all the indexes for a particular table. This script retrieves all base tables and stores them into a temporary table, then loops through rebuilding everything.

[cc lang=”sql”]
USE AdventureWorksLT2008
GO

SELECT
RowNum = ROW_NUMBER() OVER(ORDER BY t.TABLE_NAME)
,TableName = t.TABLE_SCHEMA + ‘.’ + t.TABLE_NAME
,AlterMe = ‘ALTER INDEX ALL ON [‘ + t.TABLE_SCHEMA + ‘].[‘ + t.TABLE_NAME + ‘] REBUILD;’
INTO #Reindex_Tables
FROM INFORMATION_SCHEMA.TABLES t
WHERE TABLE_TYPE = ‘BASE TABLE’

DECLARE @Iter INT
DECLARE @MaxIndex INT
DECLARE @ExecMe VARCHAR(MAX)

SET @Iter = 1
SET @MaxIndex =
(
SELECT COUNT(1)
FROM #Reindex_Tables
)

WHILE @Iter < @MaxIndex BEGIN SET @ExecMe = ( SELECT AlterMe FROM #Reindex_Tables WHERE RowNum = @Iter ) EXEC (@ExecMe) PRINT @ExecMe + ' Executed' SET @Iter = @Iter + 1 END [/cc]

4 comments
David Gage 26 Jul 2011 at 3:13 pm

Thanks for the very handy script. One point is that the loop is missing the last table. If you change ‘SET @MaxIndex = ‘ to ‘SET @MaxIndex = 1 + ‘ then you will get all of the tables.

Andrew 15 Jun 2011 at 4:39 am

Dude “<="

William 01 Nov 2010 at 6:07 am

I recommend surrounding the table name with brackets. (I had a table which used a reserved word, and the command produced an error.)

,AlterMe = ‘ALTER INDEX ALL ON ‘ + t.TABLE_SCHEMA + ‘.[‘ + t.TABLE_NAME + ‘] REBUILD;’

Derek Dieter 01 Nov 2010 at 8:37 am

Good point, thanks. I updated the script.

Derek

Featured Articles

 Site Author

  • Thanks for visiting!