My site is ad supported.

Reindex SQL Database (Script)

Version 1.0 - Download

The raw script you can copy-paste into SQL Server Management Studio is at the bottom of this page.


Description

Given a database name and (optional) SQL Server, this scripts rebuilds all active indexes for all user defined objects (tables, views, etc.).

This script is compatible with SQL Server 2005 and greater.

Requirements:

  • SqlCmd.exe (should already be registered as part of your SQL Server installation)

Examples

The following command rebuilds all indexes in the SQL Server Database named MyDb which is on the default instance of the local machine:

ReindexSqlDatabase MyDb

The following command rebuilds all indexes in the SQL Server Database named "My Database" which is on the named instance "SQLExpress" on the local machine:

ReindexSqlDatabase "My Database" .\SQLExpress

The following command outputs the SQL script used to reindex the database to the file "C:\Tools\Reindex.sql". From here you can use the script inside of SQL Server Management Studio or elsewhere:

ReindexSqlDatabase /OutputScript > C:\Tools\Reindex.sql

 

Help Information (/?)

ReindexSqlDatabase {Database [Server] | /OutputScript}

 Database [Server]
          Name of the SQL database to be backed up and its respective Server.
          If the Server is omitted, the default instance on the local machine
          will be used.
          Note that the SQL commands will be run using Windows Authentication
          so this script must be run as a user which has the appropriate
          access to the respective database.
 /OutputScript
          Outputs the SQL script which is used to the console.
          You can redirect this output to a text file (e.g. Reindex.sql) and
          use at your convenience.


My site is ad supported.

Reindex Database SQL Script

Below is the source script which is run by the command line script. This can be copy-pasted into SQL Server Management Studio to run the reindex process.

/* SQL Database Reindex Script.
   Part of the ReindexSqlDatabase.bat utility by Jason Faulkner.
   http://jasonfaulkner.com

   Run against a single SQL database.
*/

DECLARE IndexCursor CURSOR FOR
    SELECT sys.indexes.name AS IndexName
        ,sys.objects.name AS TableName
    FROM sys.indexes
    INNER JOIN sys.objects
        ON sys.indexes.object_id = sys.objects.object_id
    WHERE sys.objects.type = 'U'
        AND sys.indexes.is_disabled = 0
        AND NOT sys.indexes.name IS NULL
    ORDER BY TableName ASC
        ,IndexName ASC;
             
DECLARE @IndexName nvarchar(max), @TableName nvarchar(max);
DECLARE @ExecSql nvarchar(max);

OPEN IndexCursor;

FETCH NEXT FROM IndexCursor INTO @IndexName, @TableName;
WHILE @@FETCH_STATUS = 0
BEGIN
    PRINT @TableName + '.' + @IndexName;

    SET @ExecSql = 'ALTER INDEX [' + @IndexName + '] ON [' + @TableName + '] REBUILD;';
    EXEC (@ExecSql);

    FETCH NEXT FROM IndexCursor INTO @IndexName, @TableName;
END

CLOSE IndexCursor;
DEALLOCATE IndexCursor;