Script SQL : [MS SQL Server] Rebuild / Reorganize automatique des indexes de la base de données

CREATE PROCEDURE [dbo].[ps_reinitIndex]AS-- On ferme le curseur si il n'a pas été fermé correctement précedemmentIF CURSOR_STATUS('local','c_index') >= 0 OR CURSOR_STATUS('global','c_index') > 0 OR CURSOR_STATUS('variable','c_index') > 0BEGIN CLOSE c_index;DEALLOCATE c_index;END-- déclaration du curseur qui parcourt tous les index dont la fragmentation est supérieure à 10% et dont le nombre de pages > 50DECLARE c_index CURSOR FORSELECT  sys.objects.name AS [Table Name],sys.indexes.name AS [Index Name],CAST(avg_fragmentation_in_percent AS DECIMAL (4,2)) AS [Fragmentation Percentage],CONVERT(decimal(18,2), page_count * 8 / 1024.0) AS [Total Index Size (MB)],page_count AS [Page Count]FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, 'LIMITED') IndexStatsINNER JOIN sys.objects ON sys.objects.object_id = IndexStats.object_idINNER JOIN SYS.indexesON SYS.indexes.object_id = SYS.OBJECTS.object_id AND IndexStats.index_id = sys.indexes.index_idWHERE avg_fragmentation_in_percent > 10 AND Indexes.index_id > 0 AND page_count > 50;DECLARE @tableName VARCHAR(100);DECLARE @indexName VARCHAR(100);DECLARE @fragmentation DECIMAL (4,2);DECLARE @indexSize DECIMAL (18,2);DECLARE @pageCount INT;DECLARE @alter_query VARCHAR(500);DECLARE @log VARCHAR(100);-- on ouvre un bloc TRY/CATCHBEGIN TRYOPEN c_index;FETCH NEXT FROM c_index INTO @tableName, @indexName, @fragmentation, @indexSize, @pageCount;-- on parcourt le curseurWHILE @@FETCH_STATUS = 0BEGIN-- si la fragmentation est entre 10 et 30% et le nombre de pages de l'index > 100 alors on construit une requête de réorganisation de l'indexIF (@fragmentation BETWEEN 10 AND 30) AND @pageCount > 100BEGINSET @alter_query = 'ALTER INDEX [' + @indexName + '] ON ' + @tableName + ' REORGANIZE;';SET @log = 'Index REORGANIZE: ['  + @indexName + '] . Table: ' + @tableName;END-- Si la fragmentation est > 30% et le nombre de page > 200 alors on construit une requête de reconstuction de l'indexELSE IF @fragmentation > 30 AND @pageCount > 200BEGINSET @alter_query = 'ALTER INDEX [' + @indexName + '] ON ' + @tableName + ' REBUILD;';SET @log = 'Index REBUILD: ['  + @indexName + '] . Table: ' + @tableName;END-- on execute la requêteEXEC(@alter_query);FETCH NEXT FROM c_index INTO @tableName, @indexName, @fragmentation, @indexSize, @pageCount;ENDCLOSE c_index;DEALLOCATE c_index;END TRYBEGIN CATCHIF CURSOR_STATUS('local','c_index') >= 0 OR CURSOR_STATUS('global','c_index') > 0 OR CURSOR_STATUS('variable','c_index') > 0BEGIN CLOSE c_index;DEALLOCATE c_index;END-- si erreur, alors on affiche l'erreur en consolePRINT ERROR_MESSAGE();END CATCH;GO