By: Greg Robidoux | Updated: 2022-02-23 | Comments (85) | Related: 1 | 2 | 3 | 4 | 5 | > Fragmentation and Index Maintenance
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 and 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 and 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, therefore in this tip I provided 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 Script for SQL Server 2005 and Later
The below script will work with SQL Server 2005 and later versions.
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, so you can include these or add other databases to exclude from you index maintenance routines. Also, you can change the code to include only specific databases instead.
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 and SQL Server 2019.
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
About the author
Greg Robidoux is the President and founder of Edgewood Solutions, a technology services company delivering services and solutions for Microsoft SQL Server. He is also one of the co-founders of MSSQLTips.com. Greg has been working with SQL Server since 1999, has authored numerous database-related articles, and delivered several presentations related to SQL Server. Before SQL Server, he worked on many data platforms such as DB2, Oracle, Sybase, and Informix.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
Article Last Updated: 2022-02-23
