Новый скрипт по реиндексации

Обновленный скрипт по реиндекации

use GlobalBase;
DECLARE @command nvarchar(4000); 

-- 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      'BEGIN TRY ALTER INDEX ALL ON [' + OBJECT_SCHEMA_NAME (afp.OBJECT_ID)+'].['+OBJECT_NAME(afp.OBJECT_ID) + '] REBUILD WITH (ONLINE = ON) END TRY BEGIN CATCH ALTER INDEX ALL ON [' + OBJECT_SCHEMA_NAME (afp.OBJECT_ID)+'].['+OBJECT_NAME(afp.OBJECT_ID) + '] REBUILD WITH (ONLINE=OFF) END CATCH;' AS [Инструкция T-SQL]
INTO #work_to_do
FROM  sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, 'SAMPLED') AS afp
WHERE afp.database_id = DB_ID()
AND   afp.index_type_desc IN ('CLUSTERED INDEX')
AND   (afp.avg_fragmentation_in_percent >= 15 OR afp.avg_page_space_used_in_percent <= 60)
AND   afp.page_count > 12
UNION ALL
SELECT      [Инструкция T-SQL] =
      CASE
            WHEN afp.avg_fragmentation_in_percent >= 15
            OR afp.avg_page_space_used_in_percent <= 60
            THEN 'BEGIN TRY ALTER INDEX [' + i.name + '] ON [' +OBJECT_SCHEMA_NAME (afp.OBJECT_ID)+'].['+ OBJECT_NAME(afp.OBJECT_ID) + '] REBUILD WITH (ONLINE = ON) END TRY BEGIN CATCH ALTER INDEX ['+ i.name + '] ON [' +OBJECT_SCHEMA_NAME (afp.OBJECT_ID)+'].['+ OBJECT_NAME(afp.OBJECT_ID) + '] REBUILD WITH (ONLINE=OFF) END CATCH;'
            WHEN (afp.avg_fragmentation_in_percent < 15 AND afp.avg_fragmentation_in_percent >= 10)
            OR (afp.avg_page_space_used_in_percent > 60 AND afp.avg_page_space_used_in_percent < 75)
            THEN 'ALTER INDEX [' + i.name + '] ON [' + OBJECT_SCHEMA_NAME (afp.OBJECT_ID)+'].['+OBJECT_NAME(afp.OBJECT_ID) + '] REORGANIZE;'
      END
FROM  sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, 'SAMPLED') AS afp
JOIN  sys.indexes AS i
ON    (afp.OBJECT_ID = i.OBJECT_ID AND afp.index_id = i.index_id)
AND   afp.database_id = DB_ID()
AND   afp.index_type_desc IN ('NONCLUSTERED INDEX')
AND   (
            (afp.avg_fragmentation_in_percent >= 10 AND    afp.avg_fragmentation_in_percent < 15)
      OR    (afp.avg_page_space_used_in_percent > 60 AND afp.avg_page_space_used_in_percent < 75)
      )
AND   afp.page_count > 12
AND   afp.OBJECT_ID NOT IN    (    
                             SELECT      OBJECT_ID
                             FROM  sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, 'SAMPLED')
                             WHERE database_id = DB_ID()
                             AND   index_type_desc IN ('CLUSTERED INDEX')
                             AND   (avg_fragmentation_in_percent >= 15 OR avg_page_space_used_in_percent < 60)
                             AND   page_count > 1
                        )
--ORDER BY [Инструкция T-SQL]

-- 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 @command;
-- Loop through the partitions.
WHILE (@@FETCH_STATUS = 0)
    BEGIN;
        print ''''+(@command)+'''';
        exec(@command)
        

        FETCH NEXT
           FROM partitions
           INTO @command;
    END;

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

-- Drop the temporary table.
DROP TABLE #work_to_do;