sns/Tools/SecurityCode/ClearMSSQL/rebuild_index.sql

begin
    declare @databaseName sysname = N'SN7_SERVER_SCHEMA';
    declare @rebuildFloor float = 40;

    declare @schemaName sysname;
    declare @tableName  sysname;
    declare @indexName  sysname;
    declare @fragmentation float;


    declare @command nvarchar(500);

    print N'Rebuild index started at: ' + convert( nvarchar(100), SYSDATETIME(), 20 );
    print N'-------------------------------------------------------------------------';

    -- select indexes for rebuild
    declare indexCursor cursor fast_forward local for
    select s.name schema_name, t.name table_name, i.name index_name, d.avg_fragmentation_in_percent fragmentation
    from   sys.dm_db_index_physical_stats( DB_ID(@databaseName), null, null, null, null) d
           inner join sys.tables  t on d.object_id = t.object_id
           inner join sys.schemas s on t.schema_id = s.schema_id
           inner join sys.indexes i on d.object_id = i.object_id AND d.index_id = i.index_id
    where  d.index_id > 0
           and d.avg_fragmentation_in_percent > 10
           and d.page_count > 8

    open indexCursor;

    while( 1=1 )
    begin
        fetch next from indexCursor into @schemaName, @tableName, @indexName, @fragmentation;
        if @@FETCH_STATUS <> 0 break;

        begin try
            set @command = N'ALTER INDEX ' + @indexName + N' ON ' + @databaseName + N'.' + @schemaName + N'.' + @tableName;
            if @fragmentation < @rebuildFloor
            begin
                set @command = @command + N' REORGANIZE;';
                set @command = @command + N' UPDATE STATISTICS ' + @databaseName + N'.' + @schemaName + N'.' + @tableName + N' ' + @indexName + N';';
            end
            else
            begin
                set @command = @command + N' REBUILD WITH (ONLINE = ON); ';
            end;
           
            --print @command;
            exec (@command);

            print N'INDEX ' + @indexName + N'ON ' + @databaseName + N'.' + @schemaName + N'.' + @tableName + N' SUCCESSFULLY PROCESSED';
        end try
        begin catch
            print N'ERROR REBUILD INDEX ' + @indexName;
            print N'ERROR MESSAGE: ' + ERROR_MESSAGE();
        end catch
    end;

    print N'-------------------------------------------------------------------------';
    print N'Rebuild index completed at: ' + convert( nvarchar(100), SYSDATETIME(), 20 );

    close indexCursor;
    deallocate indexCursor;
end;