Rebuild all indexes and statistics on Azure SQL database

By | July 6, 2017

Since Azure SQL databases are limited in what they expose to the management studio client we miss out on a lot of features within SSMS that we could normally use. One of the most common features we use frequently in this regard is the Rebuild Indexes and update statistics features within Maintenance Wizard.

Below is a very simple script that can be used to rebuild all indexes on all tables without regard to fragmentation. This could blow up the DTUs if you’re performing the common on a table with large number of records of in correct DTU sizing for the Azure SQL database.

SELECT 'ALTER INDEX ALL ON ['+ Table_schema+'].['+Table_name+'] REBUILD;' FROM  information_schema.tables 
WHERE Table_schema!='sys'
GO
EXEC SP_UPDATESTATS

This script is provided for use in databases managed by people who are not professional DBAs but still require scripts to perform basic tasks without having to worry about the complexity involved.

Additional References

https://thomaslarock.com/2012/03/march-madness-sql-azure-update-statistics/

http://pietschsoft.com/post/2011/11/10/SQL-Azure-REBUILD-All-Indexes-in-Database-e28093-Alternative-to-DBCC-DBREINDEX