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]