Skip to content
«
»
 

Alter Index All Tables

Posted by 
on  June 13 2010

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.

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


Popular search terms:
Filed under DBA
Comment (RSS)  |  Trackback  |  Permalink

4 Comments

  1. David Gage says:
    July 26, 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.

    Reply to this comment
  2. Andrew says:
    June 15, 2011 at 4:39 am

    Dude “<="

    Reply to this comment
  3. William says:
    November 1, 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;’

    Reply to this comment

post a comment OR Post Your Question on our ASK! Community!

«
»