Alter Index All Tables

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.

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