Reindezierung aller Indizes

Alle Indizes mittels T-SQL neu erstellen.

Das Skript von Jason Faulkner indeziert alle Indizes, aber nur mit dem Standardschema. Das ist die schemaübergreifende Version des Skriptes.

/* 

    SQL Rexindex Script

    Christian Paul, Mai 2016

    Based on

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

*/

DECLARE IndexCursor CURSOR FOR
    SELECT  sys.indexes.name AS IndexName
         ,sys.objects.name AS TableName
         ,sys.schemas.name AS SchemaName
    FROM sys.indexes
    INNER JOIN sys.objects ON sys.indexes.object_id = sys.objects.object_id
    INNER JOIN sys.schemas ON sys.objects.schema_id = sys.schemas.schema_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),
       @ExecSql nvarchar(max),
       @SchemaName nvarchar(max);

OPEN IndexCursor;

FETCH NEXT FROM IndexCursor INTO @IndexName, @TableName, @SchemaName;
WHILE @@FETCH_STATUS = 0
BEGIN

    SET @ExecSql = 'ALTER INDEX [' + @IndexName + '] ON [' + @SchemaName + '].[' + @TableName + '] REBUILD;';

    PRINT @ExecSql;
    EXEC (@ExecSql);

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

CLOSE IndexCursor;
DEALLOCATE IndexCursor;