There comes a time when you have to rebuild all the indexes in a database on SQL Server, and you have to figure out a quick way of doing this. You can set up different jobs and routines to schedule the rebuild, but it is handy to have a script.
You have to rebuild indexes when you are the administrator of a database and realised it is going very slow after you have done some investigation, and you have found out that databases have not been build for a very long time (or never).
If you want a quick way to rebuild your indexes on SQL Server using a script, then use the script below.
This script will find all the indexes within a database and rebuild them. You can change the script if you like to add ONLINE rebuilding if you want your tables to be accessible through the rebuild. I have used this index rebuild many times to speed up the process of fixing performance related problem within a database when you know that there has not been a proper maintenance plan set up for the database.
This script on SQL Server 2012, SQL Server 2016 and SQL Server 2017. I have tried this script on these editions and they work and probably will work on future editions.
I would try this script out in a development environment first just to make sure you are happy with the performance because it will have a performance hit on your server when this is running. It may also take a while to run the script as this is dependant on how big your tables are and how many indexes you have.
SQL Server Rebuilding Indexes Code
DECLARE @Database VARCHAR(255) DECLARE @Table VARCHAR(255) DECLARE @cmd NVARCHAR(500) DECLARE @fillfactor INT SET @fillfactor = 100 DECLARE DatabaseCursor CURSOR FOR SELECT name FROM sys.databases ORDER BY 1 OPEN DatabaseCursor FETCH NEXT FROM DatabaseCursor INTO @Database WHILE @@FETCH_STATUS = 0 BEGIN SET @cmd = 'DECLARE TableCursor CURSOR 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 IF (@@MICROSOFTVERSION / POWER(2, 24) >= 9) BEGIN -- SQL 2005 or higher command SET @cmd = 'ALTER INDEX ALL ON ' + @Table + ' REBUILD ' --PRINT @cmd EXEC (@cmd) END ELSE BEGIN -- SQL 2000 command --DBCC DBREINDEX(@Table,' ',@fillfactor) PRINT 1 END FETCH NEXT FROM TableCursor INTO @Table END CLOSE TableCursor DEALLOCATE TableCursor FETCH NEXT FROM DatabaseCursor INTO @Database END CLOSE DatabaseCursor DEALLOCATE DatabaseCursor
Why not try it and give it a go to see if it works for you?
More tools like this are included in the SQL Server Bootcamp course
Please leave a comment below if this script to rebuild your indexes work or what improvements you could do to it.
Very nicely done and helpful.. Thank you !