Rebuild all SQL Server Indexes for all Tables in all Databases

Problem

One of the main functions of a DBA is to maintain indexes. There have been several tips written about different commands to use for index rebuilds and index reorgs, as well as the differences between index maintenance with SQL Server. In addition, other tips have been written about using maintenance plans to maintain indexes on all databases. One of the issues with maintenance plans is that they don’t always seem to be as reliable as you would hope. You also sometimes get false feedback on whether the task actually was successful or not. In this tip, we look at a simple script that could be used to rebuild all indexes for all databases.

Solution

The one nice thing about maintenance plans is that they work across multiple databases. Therefore, you can push out one task to handle the same activity across all of your databases. The problem that I have seen with maintenance plans is that sometimes they do not work as expected. This tip provides another approach.

The script below allows you to rebuild indexes for all databases and all tables within a database. This could be further tweaked to handle only indexes that need maintenance based on fragmentation levels as well as then doing either an index reorg or an index rebuild.

The script uses two cursors, one for the databases and another for the tables, within the database.

Rebuild All Indexes in all Databases for a SQL Server Instance

The below script will rebuild all indexes for all databases for versions SQL Server 2005 through SQL Server 2022.

Because we need to change from database to database, we need to create dynamic SQL code for the queries. The code uses master.sys.databases to get a list of databases, as well as check the status for the database, to make sure we are working with databases that are online. This also uses INFORMATION_SCHEMA.TABLES to get a list of user tables in the database.

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')  -- 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' 
         --PRINT @cmd -- uncomment if you want to see commands
         EXEC (@cmd) 
      END TRY
      BEGIN CATCH
         PRINT '---'
         PRINT @cmd
         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

Notes

I have excluded the system databases. You can include these or add other databases to exclude from your index maintenance routines. Also, you can change the code to include only specific databases.

This script has been tested and will work with SQL Server 2005, SQL Server 2008, SQL Server 2008R2, SQL Server 2012, SQL Server 2014, SQL Server 2016, SQL Server 2017, SQL Server 2019 and SQL Server 2022.

Next Steps

• This is a simple base script that could be modified into a stored procedure and also allow you to pass other parameters such as doing an index rebuild or an index defrag.
• Make the index rebuild statements more robust with other options.
• You could also modify this to read from a table that you create to identify which databases and which indexes you want to run this against. You can look at index fragmentation and only rebuild the indexes that need to be rebuilt.
• This approach rebuilds all indexes, so be careful if you run this on very large indexes or large databases because it will take some time to complete and consume memory, cpu and disk resources.
• Take a look at these other index related tips