By: Greg Robidoux | Updated: 2019-05-22 | Comments (76) | Fragmentation and Index Maintenance
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.
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
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 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
- Take a look at these other index related tips
Last Updated: 2019-05-22
About the author
View all my tips