Последняя реализация индексации

SET NOCOUNT ON;

DECLARE @objectid int;
DECLARE @indexid int;
DECLARE @partitioncount bigint;
DECLARE @schemaname nvarchar(130); 
DECLARE @objectname nvarchar(130); 
DECLARE @indexname nvarchar(130); 
DECLARE @partitionnum bigint;
DECLARE @partitions bigint;
DECLARE @frag float;
DECLARE @command nvarchar(4000); 
declare @datetime1 datetime;
declare @datetime2 datetime;
declare @time time;

declare @Rdatetime1 datetime;
declare @Rdatetime2 datetime;
declare @Rtime time;

DECLARE @p_body as nvarchar(max), @p_subject as nvarchar(max)
DECLARE @p_recipients as nvarchar(max), @p_profile_name as nvarchar(max)

SET @p_profile_name = N'sql'
SET @p_recipients = N'dyachok.nickolay@agromars.com; Oleynik@agromars.com'
SET @p_subject = N'Отчет по работе с индексами'
SET @Rdatetime1 = getdate()
SET @p_body = N'Начало отчета:' + CONVERT(nvarchar(30),@Rdatetime1,126)+N'<BR>'
BEGIN TRY
-- Conditionally select tables and indexes from the sys.dm_db_index_physical_stats function 
-- and convert object and index IDs to names.
IF OBJECT_ID('#work_to_do') IS NOT NULL
    DROP TABLE #work_to_do;

SELECT
    object_id AS objectid,
    index_id AS indexid,
    partition_number AS partitionnum,
    avg_fragmentation_in_percent AS frag
INTO #work_to_do
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, 'LIMITED')
WHERE avg_fragmentation_in_percent > 5.0 AND index_id > 0
order by avg_fragmentation_in_percent desc;

-- Declare the cursor for the list of partitions to be processed.
DECLARE partitions CURSOR FOR SELECT * FROM #work_to_do;

-- Open the cursor.
OPEN partitions;
FETCH NEXT
           FROM partitions
           INTO @objectid, @indexid, @partitionnum, @frag;
-- Loop through the partitions.
WHILE (@@FETCH_STATUS = 0)
    BEGIN;
        
        --IF @@FETCH_STATUS < 0 BREAK;
        SELECT @objectname = QUOTENAME(o.name), @schemaname = QUOTENAME(s.name)
        FROM sys.objects AS o
        JOIN sys.schemas as s ON s.schema_id = o.schema_id
        WHERE o.object_id = @objectid;
        SELECT @indexname = QUOTENAME(name)
        FROM sys.indexes
        WHERE  object_id = @objectid AND index_id = @indexid;
        SELECT @partitioncount = count (*)
        FROM sys.partitions
        WHERE object_id = @objectid AND index_id = @indexid;
        --PRINT N'Execute: ' + @command
            -- 30 is an arbitrary decision point at which to switch between reorganizing and rebuilding.
        IF @frag < 30.0
            SET @command = N'BEGIN TRY ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REORGANIZE END TRY BEGIN CATCH END CATCH;';
        IF @frag >= 30.0
            SET @command = N'BEGIN TRY ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REBUILD  WITH (ONLINE=ON) END TRY BEGIN CATCH ALTER INDEX '+@indexname+N' ON '+ @schemaname+N'.'+ @objectname+ N' REBUILD WITH (ONLINE=OFF) END CATCH;';
        IF @partitioncount > 1
            SET @command = @command + N' PARTITION=' + CAST(@partitionnum AS nvarchar(10));
        set @datetime1 = getdate();
        EXEC (@command);
        set @datetime2 = getdate();
        set @time  = @datetime2-@datetime1;
        
        SET @p_body = @p_body + N'Executed: ' + @command+N' Затрачено время:'+CAST(@time as NvarChar(13))+N'<BR>';
        FETCH NEXT
           FROM partitions
           INTO @objectid, @indexid, @partitionnum, @frag;
    END;

-- Close and deallocate the cursor.
CLOSE partitions;
DEALLOCATE partitions;

-- Drop the temporary table.
DROP TABLE #work_to_do;
END TRY
BEGIN CATCH
SET @p_body = @p_body + N'<BR>!!!!Ошибка выполнения:' + ERROR_NUMBER() + N' '+ERROR_SEVERITY() + N' '+ ERROR_STATE()+N' '+ERROR_LINE() + N' ' +ERROR_PROCEDURE() + N' '+ERROR_MESSAGE()
END CATCH

SET @Rdatetime2 = getdate() 
set @Rtime  = @Rdatetime2-@Rdatetime1;
SET @p_body = @p_body + N'Завершение отчета:' + CONVERT(nvarchar(30), @Rdatetime2, 126) + N' Время выполенения:' + CAST(@Rtime as NvarChar(13))+N'<BR>'

EXEC msdb.dbo.sp_send_dbmail
   @profile_name = @p_profile_name,
   @recipients = @p_recipients,
   @body = @p_body,
   @body_format = 'HTML',
   @subject = @p_subject

GO