Rebuild Indexes in a SQL database for all the tables
To reorganize or rebuild indexes in a SQL database is a common task for SQL DBA. Today, we will be discussing how to script out rebuild statement for all the indexes in entire databases.
SELECT 'ALTER INDEX ALL ON ' + t.[TABLE_NAME] + ' REBUILD; '
FROM INFORMATION_SCHEMA.TABLES t
WHERE t.[TABLE_TYPE] =
'BASE TABLE'
ORDER BY t.[TABLE_NAME];
Make sure you run the script against the proper database as this is a database specific query.
Once you run this script against the required database you will get the actual scripts which will rebuild the indexes. Sample output is pasted below-
ALTER INDEX ALL ON DIY_CheckList REBUILD;
ALTER INDEX ALL ON Address REBUILD;
ALTER INDEX ALL ON DIY_Question REBUILD;
ALTER INDEX ALL ON DIY_QuestionA REBUILD;
Copy these scripts and start the execution.
Please note that rebuild indexes is recommended to be performed in the off business hours.
