Showing posts with label Rebuild indexes in a SQL database. Show all posts
Showing posts with label Rebuild indexes in a SQL database. Show all posts

Tuesday, 6 June 2017

Rebuild indexes in a SQL database

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.