Rebuilding Indexes Using A Script On SQL Server

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.