SET NOCOUNT ON;
DECLARE @DB_name VARCHAR(250);  
DECLARE @DB_id SMALLINT;
DECLARE @cmd VARCHAR(1000);  

DECLARE DatabaseCursor CURSOR READ_ONLY FOR  
	SELECT [name],[database_id] FROM master.sys.databases WHERE database_id NOT IN (1,3,4,5,6,122,124)
	AND state = 0 AND is_in_standby = 0 ORDER BY 1  

OPEN DatabaseCursor  

	FETCH NEXT FROM DatabaseCursor INTO @DB_name, @DB_id
	WHILE @@FETCH_STATUS = 0  
	BEGIN  
	SET @cmd = 'EXEC [BASE].[dbo].[ReIndex] @DbId = ' + CAST(@DB_id as varchar(10)) + ';';
	PRINT('[' + @DB_name + '] ' + @cmd);
	BEGIN TRY
		EXEC (@cmd);  
	END TRY
	BEGIN CATCH
		SELECT @DB_name, @DB_id, ERROR_MESSAGE();
	END CATCH
  	   FETCH NEXT FROM DatabaseCursor INTO @DB_name, @DB_id  
	END 

CLOSE DatabaseCursor   
DEALLOCATE DatabaseCursor

USE [BASE]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[ReIndex] @DbId INT AS BEGIN

SET NOCOUNT ON; 
	DECLARE @RowNumber INT = 1, @CntRows INT = 0;
	DECLARE @TableName varchar(300) = '', @IndexName varchar(300) = '', @AvgFrag FLOAT = 0;
	DECLARE @Command VARCHAR(8000) = '';
       
	if (@DbId is null) begin return	end
		create table #IndexTmpTable (Id INT IDENTITY(1,1) PRIMARY KEY,TableName varchar(150),IndexName varchar(300),AvgFrag FLOAT);

		SELECT @Command = 'use [' + DB_NAME(@DbId) + '];
		INSERT INTO #IndexTmpTable
        SELECT Obj.name AS TableName, Inx.name AS IndexName, AvgFrag.avg_fragmentation_in_percent AS Fragmentation
		FROM sys.dm_db_index_physical_stats (' + CAST(@DbId as varchar(10)) + ', NULL, NULL, NULL, NULL) AS AvgFrag
			LEFT JOIN sys.indexes AS Inx ON AvgFrag.object_id = Inx.object_id AND AvgFrag.index_id = Inx.index_id
			LEFT JOIN sys.objects AS Obj ON AvgFrag.object_id = Obj.object_id
			LEFT JOIN sys.schemas AS Sch ON Obj.schema_id = Sch.schema_id
		WHERE AvgFrag.index_id > 0
			AND AvgFrag.avg_fragmentation_in_percent > 1'
		EXEC (@Command);

        --Количество строк для обработки
        SELECT @CntRows = COUNT(1) FROM #IndexTmpTable
		
        --Цикл обработки каждого индекса
        WHILE @RowNumber <= @CntRows
                BEGIN
                  --Получаем названия объектов, а также степень фрагментации текущего индекса
                  SELECT @TableName = TableName, 
                         @IndexName = IndexName, 
                         @AvgFrag = AvgFrag
                  FROM #IndexTmpTable
                  WHERE Id = @RowNumber    
                  IF (@AvgFrag > 1) BEGIN
                       --Формируем строку инструкции и выполняем ее
                       SELECT @Command = 'ALTER INDEX [' + @IndexName + '] ON [' + DB_NAME(@DbId) + '].[dbo].[' + @TableName + '] REORGANIZE';
                       EXEC (@Command);
				END 
                   --Выводим служебную информацию о текущей операции
                   PRINT(CAST(@AvgFrag AS VARCHAR(10)) + '%fragm Выполнена инструкция ' + @Command);     
                   --Переходим к следующему индексу
                   SET @RowNumber = @RowNumber + 1
    END
	drop table #IndexTmpTable;
END