Within a single database:
Exec sp_msforeachtable 'SET QUOTED_IDENTIFIER ON; ALTER INDEX ALL ON ? REBUILD'
Or for a full rebuild across multiple 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
Feedback sent
We appreciate your effort and will try to fix the article