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
