Rebuild all indexes on SQL Server

Created by Jeremy Burgess, Modified on Wed, 2 Mar, 2022 at 1:25 PM by Jeremy Burgess

Within a single database:

Exec sp_msforeachtable 'SET QUOTED_IDENTIFIER ON; ALTER INDEX ALL ON ? REBUILD'


Or for a full rebuild across multiple databases.


Source: https://www.mssqltips.com/sqlservertip/1367/sql-server-script-to-rebuild-all-indexes-for-all-tables-and-all-databases/


DECLARE @Database NVARCHAR(255)
DECLARE @Table NVARCHAR(255)
DECLARE @cmd NVARCHAR(1000)

DECLARE DatabaseCursor CURSOR READ_ONLY
FOR
SELECT name
FROM master.sys.databases
WHERE name NOT IN (
		'master'
		,'msdb'
		,'tempdb'
		,'model'
		,'distribution'
		,'rdsadmin'
		) -- databases to exclude
	--WHERE name IN ('DB1', 'DB2') -- use this to select specific databases and comment out line above
	AND STATE = 0 -- database is online
	AND is_in_standby = 0 -- database is not read only for log shipping
ORDER BY 1

OPEN DatabaseCursor

FETCH NEXT
FROM DatabaseCursor
INTO @Database

WHILE @@FETCH_STATUS = 0
BEGIN
	SET @cmd = 'DECLARE TableCursor CURSOR READ_ONLY FOR SELECT ''['' + table_catalog + ''].['' + table_schema + ''].['' +  
   table_name + '']'' as tableName FROM [' + @Database + '].INFORMATION_SCHEMA.TABLES WHERE table_type = ''BASE TABLE'''

	-- create table cursor  
	EXEC (@cmd)

	OPEN TableCursor

	FETCH NEXT
	FROM TableCursor
	INTO @Table

	WHILE @@FETCH_STATUS = 0
	BEGIN
		BEGIN TRY
			SET @cmd = 'ALTER INDEX ALL ON ' + @Table + ' REBUILD' -- with (sort_in_tempdb = on, fillfactor = 90)

			RAISERROR (
					@cmd
					,10
					,1
					) -- uncomment if you want to see commands

			EXEC (@cmd)
		END TRY

		BEGIN CATCH
			PRINT '---'
			PRINT ERROR_MESSAGE()
			PRINT '---'
		END CATCH

		FETCH NEXT
		FROM TableCursor
		INTO @Table
	END

	CLOSE TableCursor

	DEALLOCATE TableCursor

	FETCH NEXT
	FROM DatabaseCursor
	INTO @Database
END

CLOSE DatabaseCursor

DEALLOCATE DatabaseCursor


Was this article helpful?

That’s Great!

Thank you for your feedback

Sorry! We couldn't be helpful

Thank you for your feedback

Let us know how can we improve this article!

Select at least one of the reasons
CAPTCHA verification is required.

Feedback sent

We appreciate your effort and will try to fix the article