T-SQL: Rebuild or Reorganize Indexes for All Tables in a Database

Using the handy-dandy hidden Microsoft goodie “sp_msForEachTable”, you can easily rebuild all indexes on all tables in a user database with a one line command. Here’s how:

-- To rebuild with the default fill-factor
EXECUTE sp_msForEachTable 'SET QUOTED_IDENTIFIER ON; ALTER INDEX ALL ON ? REBUILD;'
 
-- Or change one keyword to reorganize the indexes
EXECUTE sp_msForEachTable 'SET QUOTED_IDENTIFIER ON; ALTER INDEX ALL ON ? REORGANIZE;'

Hope this helps!

This entry was posted in T-SQL. Bookmark the permalink.

One Response to T-SQL: Rebuild or Reorganize Indexes for All Tables in a Database

  1. Mike says:

    You can wrap this in a try/catch as well

    EXECUTE sp_msForEachTable ‘SET QUOTED_IDENTIFIER ON;
    BEGIN Try
    ALTER INDEX ALL ON ? REBUILD;
    PRINT ”Rebuilt ?”
    END Try
    BEGIN Catch
    PRINT ”Could not rebuild ?”
    END Catch

Leave a Reply

Your email address will not be published. Required fields are marked *