ÿþSET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE PROCEDURE [dbo].[IndexOptimize]



@Databases nvarchar(max),

@FragmentationLow nvarchar(max) = NULL,

@FragmentationMedium nvarchar(max) = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',

@FragmentationHigh nvarchar(max) = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',

@FragmentationLevel1 int = 5,

@FragmentationLevel2 int = 30,

@PageCountLevel int = 1000,

@SortInTempdb nvarchar(max) = 'N',

@MaxDOP int = NULL,

@FillFactor int = NULL,

@PadIndex nvarchar(max) = NULL,

@LOBCompaction nvarchar(max) = 'Y',

@UpdateStatistics nvarchar(max) = NULL,

@OnlyModifiedStatistics nvarchar(max) = 'N',

@StatisticsSample int = NULL,

@StatisticsResample nvarchar(max) = 'N',

@PartitionLevel nvarchar(max) = 'N',

@TimeLimit int = NULL,

@Indexes nvarchar(max) = NULL,

@Delay int = NULL,

@LogToTable nvarchar(max) = 'N',

@Execute nvarchar(max) = 'Y'



AS



BEGIN



  ----------------------------------------------------------------------------------------------------

  --// Source: http://ola.hallengren.com                                                          //--

  ----------------------------------------------------------------------------------------------------



  SET NOCOUNT ON



  SET ARITHABORT ON



  SET LOCK_TIMEOUT 3600000



  DECLARE @StartMessage nvarchar(max)

  DECLARE @EndMessage nvarchar(max)

  DECLARE @DatabaseMessage nvarchar(max)

  DECLARE @ErrorMessage nvarchar(max)



  DECLARE @Version numeric(18,10)



  DECLARE @StartTime datetime



  DECLARE @CurrentIndexList nvarchar(max)

  DECLARE @CurrentIndexItem nvarchar(max)

  DECLARE @CurrentIndexPosition int



  DECLARE @CurrentID int

  DECLARE @CurrentDatabaseID int

  DECLARE @CurrentDatabaseName nvarchar(max)

  DECLARE @CurrentIsDatabaseAccessible bit

  DECLARE @CurrentMirroringRole nvarchar(max)



  DECLARE @CurrentCommand01 nvarchar(max)

  DECLARE @CurrentCommand02 nvarchar(max)

  DECLARE @CurrentCommand03 nvarchar(max)

  DECLARE @CurrentCommand04 nvarchar(max)

  DECLARE @CurrentCommand05 nvarchar(max)

  DECLARE @CurrentCommand06 nvarchar(max)

  DECLARE @CurrentCommand07 nvarchar(max)

  DECLARE @CurrentCommand08 nvarchar(max)

  DECLARE @CurrentCommand09 nvarchar(max)

  DECLARE @CurrentCommand10 nvarchar(max)

  DECLARE @CurrentCommand11 nvarchar(max)

  DECLARE @CurrentCommand12 nvarchar(max)



  DECLARE @CurrentCommandOutput09 int

  DECLARE @CurrentCommandOutput10 int



  DECLARE @CurrentCommandType09 nvarchar(max)

  DECLARE @CurrentCommandType10 nvarchar(max)



  DECLARE @CurrentIxID int

  DECLARE @CurrentSchemaID int

  DECLARE @CurrentSchemaName nvarchar(max)

  DECLARE @CurrentObjectID int

  DECLARE @CurrentObjectName nvarchar(max)

  DECLARE @CurrentObjectType nvarchar(max)

  DECLARE @CurrentIndexID int

  DECLARE @CurrentIndexName nvarchar(max)

  DECLARE @CurrentIndexType int

  DECLARE @CurrentStatisticsID int

  DECLARE @CurrentStatisticsName nvarchar(max)

  DECLARE @CurrentPartitionID bigint

  DECLARE @CurrentPartitionNumber int

  DECLARE @CurrentPartitionCount int

  DECLARE @CurrentIsPartition bit

  DECLARE @CurrentIndexExists bit

  DECLARE @CurrentStatisticsExists bit

  DECLARE @CurrentIsImageText bit

  DECLARE @CurrentIsNewLOB bit

  DECLARE @CurrentIsFileStream bit

  DECLARE @CurrentAllowPageLocks bit

  DECLARE @CurrentNoRecompute bit

  DECLARE @CurrentStatisticsModified bit

  DECLARE @CurrentOnReadOnlyFileGroup bit

  DECLARE @CurrentFragmentationLevel float

  DECLARE @CurrentPageCount bigint

  DECLARE @CurrentFragmentationGroup nvarchar(max)

  DECLARE @CurrentAction nvarchar(max)

  DECLARE @CurrentMaxDOP int

  DECLARE @CurrentUpdateStatistics nvarchar(max)

  DECLARE @CurrentComment nvarchar(max)

  DECLARE @CurrentExtendedInfo xml

  DECLARE @CurrentDelay datetime



  DECLARE @tmpDatabases TABLE (ID int IDENTITY PRIMARY KEY,

                               DatabaseName nvarchar(max),

                               Completed bit)



  DECLARE @tmpIndexesStatistics TABLE (IxID int IDENTITY,

                                       SchemaID int,

                                       SchemaName nvarchar(max),

                                       ObjectID int,

                                       ObjectName nvarchar(max),

                                       ObjectType nvarchar(max),

                                       IndexID int,

                                       IndexName nvarchar(max),

                                       IndexType int,

                                       StatisticsID int,

                                       StatisticsName nvarchar(max),

                                       PartitionID bigint,

                                       PartitionNumber int,

                                       PartitionCount int,

                                       Selected bit,

                                       Completed bit,

                                       PRIMARY KEY(Selected, Completed, IxID))



  DECLARE @SelectedIndexes TABLE (DatabaseName nvarchar(max),

                                  SchemaName nvarchar(max),

                                  ObjectName nvarchar(max),

                                  IndexName nvarchar(max),

                                  Selected bit)



  DECLARE @Actions TABLE ([Action] nvarchar(max))



  INSERT INTO @Actions([Action]) VALUES('INDEX_REBUILD_ONLINE')

  INSERT INTO @Actions([Action]) VALUES('INDEX_REBUILD_OFFLINE')

  INSERT INTO @Actions([Action]) VALUES('INDEX_REORGANIZE')



  DECLARE @ActionsPreferred TABLE (FragmentationGroup nvarchar(max),

                                   [Priority] int,

                                   [Action] nvarchar(max))



  DECLARE @CurrentActionsAllowed TABLE ([Action] nvarchar(max))



  DECLARE @Error int

  DECLARE @ReturnCode int



  SET @Error = 0

  SET @ReturnCode = 0



  SET @Version = CAST(LEFT(CAST(SERVERPROPERTY('ProductVersion') AS nvarchar(max)),CHARINDEX('.',CAST(SERVERPROPERTY('ProductVersion') AS nvarchar(max))) - 1) + '.' + REPLACE(RIGHT(CAST(SERVERPROPERTY('ProductVersion') AS nvarchar(max)), LEN(CAST(SERVERPROPERTY('ProductVersion') AS nvarchar(max))) - CHARINDEX('.',CAST(SERVERPROPERTY('ProductVersion') AS nvarchar(max)))),'.','') AS numeric(18,10))



  ----------------------------------------------------------------------------------------------------

  --// Log initial information                                                                    //--

  ----------------------------------------------------------------------------------------------------



  SET @StartTime = CONVERT(datetime,CONVERT(nvarchar,GETDATE(),120),120)



  SET @StartMessage = 'DateTime: ' + CONVERT(nvarchar,@StartTime,120) + CHAR(13) + CHAR(10)

  SET @StartMessage = @StartMessage + 'Server: ' + CAST(SERVERPROPERTY('ServerName') AS nvarchar) + CHAR(13) + CHAR(10)

  SET @StartMessage = @StartMessage + 'Version: ' + CAST(SERVERPROPERTY('ProductVersion') AS nvarchar) + CHAR(13) + CHAR(10)

  SET @StartMessage = @StartMessage + 'Edition: ' + CAST(SERVERPROPERTY('Edition') AS nvarchar) + CHAR(13) + CHAR(10)

  SET @StartMessage = @StartMessage + 'Procedure: ' + QUOTENAME(DB_NAME(DB_ID())) + '.' + (SELECT QUOTENAME(schemas.name) FROM sys.schemas schemas INNER JOIN sys.objects objects ON schemas.[schema_id] = objects.[schema_id] WHERE [object_id] = @@PROCID) + '.' + QUOTENAME(OBJECT_NAME(@@PROCID)) + CHAR(13) + CHAR(10)

  SET @StartMessage = @StartMessage + 'Parameters: @Databases = ' + ISNULL('''' + REPLACE(@Databases,'''','''''') + '''','NULL')

  SET @StartMessage = @StartMessage + ', @FragmentationLow = ' + ISNULL('''' + REPLACE(@FragmentationLow,'''','''''') + '''','NULL')

  SET @StartMessage = @StartMessage + ', @FragmentationMedium = ' + ISNULL('''' + REPLACE(@FragmentationMedium,'''','''''') + '''','NULL')

  SET @StartMessage = @StartMessage + ', @FragmentationHigh = ' + ISNULL('''' + REPLACE(@FragmentationHigh,'''','''''') + '''','NULL')

  SET @StartMessage = @StartMessage + ', @FragmentationLevel1 = ' + ISNULL(CAST(@FragmentationLevel1 AS nvarchar),'NULL')

  SET @StartMessage = @StartMessage + ', @FragmentationLevel2 = ' + ISNULL(CAST(@FragmentationLevel2 AS nvarchar),'NULL')

  SET @StartMessage = @StartMessage + ', @PageCountLevel = ' + ISNULL(CAST(@PageCountLevel AS nvarchar),'NULL')

  SET @StartMessage = @StartMessage + ', @SortInTempdb = ' + ISNULL('''' + REPLACE(@SortInTempdb,'''','''''') + '''','NULL')

  SET @StartMessage = @StartMessage + ', @MaxDOP = ' + ISNULL(CAST(@MaxDOP AS nvarchar),'NULL')

  SET @StartMessage = @StartMessage + ', @FillFactor = ' + ISNULL(CAST(@FillFactor AS nvarchar),'NULL')

  SET @StartMessage = @StartMessage + ', @PadIndex = ' + ISNULL('''' + REPLACE(@PadIndex,'''','''''') + '''','NULL')

  SET @StartMessage = @StartMessage + ', @LOBCompaction = ' + ISNULL('''' + REPLACE(@LOBCompaction,'''','''''') + '''','NULL')

  SET @StartMessage = @StartMessage + ', @UpdateStatistics = ' + ISNULL('''' + REPLACE(@UpdateStatistics,'''','''''') + '''','NULL')

  SET @StartMessage = @StartMessage + ', @OnlyModifiedStatistics = ' + ISNULL('''' + REPLACE(@OnlyModifiedStatistics,'''','''''') + '''','NULL')

  SET @StartMessage = @StartMessage + ', @StatisticsSample = ' + ISNULL(CAST(@StatisticsSample AS nvarchar),'NULL')

  SET @StartMessage = @StartMessage + ', @StatisticsResample = ' + ISNULL('''' + REPLACE(@StatisticsResample,'''','''''') + '''','NULL')

  SET @StartMessage = @StartMessage + ', @PartitionLevel = ' + ISNULL('''' + REPLACE(@PartitionLevel,'''','''''') + '''','NULL')

  SET @StartMessage = @StartMessage + ', @TimeLimit = ' + ISNULL(CAST(@TimeLimit AS nvarchar),'NULL')

  SET @StartMessage = @StartMessage + ', @Indexes = ' + ISNULL('''' + REPLACE(@Indexes,'''','''''') + '''','NULL')

  SET @StartMessage = @StartMessage + ', @Delay = ' + ISNULL(CAST(@Delay AS nvarchar),'NULL')

  SET @StartMessage = @StartMessage + ', @LogToTable = ' + ISNULL('''' + REPLACE(@LogToTable,'''','''''') + '''','NULL')

  SET @StartMessage = @StartMessage + ', @Execute = ' + ISNULL('''' + REPLACE(@Execute,'''','''''') + '''','NULL') + CHAR(13) + CHAR(10)

  SET @StartMessage = @StartMessage + 'Source: http://ola.hallengren.com' + CHAR(13) + CHAR(10) + ' '

  SET @StartMessage = REPLACE(@StartMessage,'%','%%')

  RAISERROR(@StartMessage,10,1) WITH NOWAIT



  ----------------------------------------------------------------------------------------------------

  --// Check core requirements                                                                    //--

  ----------------------------------------------------------------------------------------------------



  IF SERVERPROPERTY('EngineEdition') = 5

  BEGIN

    SET @ErrorMessage = 'SQL Azure is not supported.' + CHAR(13) + CHAR(10) + ' '

    RAISERROR(@ErrorMessage,16,1) WITH NOWAIT

    SET @Error = @@ERROR

  END



  IF @Error <> 0

  BEGIN

    SET @ReturnCode = @Error

    GOTO Logging

  END



  IF NOT EXISTS (SELECT * FROM sys.objects objects INNER JOIN sys.schemas schemas ON objects.[schema_id] = schemas.[schema_id] WHERE objects.[type] = 'P' AND schemas.[name] = 'dbo' AND objects.[name] = 'CommandExecute')

  BEGIN

    SET @ErrorMessage = 'The stored procedure CommandExecute is missing. Download http://ola.hallengren.com/scripts/CommandExecute.sql.' + CHAR(13) + CHAR(10) + ' '

    RAISERROR(@ErrorMessage,16,1) WITH NOWAIT

    SET @Error = @@ERROR

  END



  IF EXISTS (SELECT * FROM sys.objects objects INNER JOIN sys.schemas schemas ON objects.[schema_id] = schemas.[schema_id] WHERE objects.[type] = 'P' AND schemas.[name] = 'dbo' AND objects.[name] = 'CommandExecute' AND OBJECT_DEFINITION(objects.[object_id]) NOT LIKE '%@LogToTable%')

  BEGIN

    SET @ErrorMessage = 'The stored procedure CommandExecute needs to be updated. Download http://ola.hallengren.com/scripts/CommandExecute.sql.' + CHAR(13) + CHAR(10) + ' '

    RAISERROR(@ErrorMessage,16,1) WITH NOWAIT

    SET @Error = @@ERROR

  END



  IF NOT EXISTS (SELECT * FROM sys.objects objects INNER JOIN sys.schemas schemas ON objects.[schema_id] = schemas.[schema_id] WHERE objects.[type] = 'TF' AND schemas.[name] = 'dbo' AND objects.[name] = 'DatabaseSelect')

  BEGIN

    SET @ErrorMessage = 'The function DatabaseSelect is missing. Download http://ola.hallengren.com/scripts/DatabaseSelect.sql.' + CHAR(13) + CHAR(10) + ' '

    RAISERROR(@ErrorMessage,16,1) WITH NOWAIT

    SET @Error = @@ERROR

  END



  IF @LogToTable = 'Y' AND NOT EXISTS (SELECT * FROM sys.objects objects INNER JOIN sys.schemas schemas ON objects.[schema_id] = schemas.[schema_id] WHERE objects.[type] = 'U' AND schemas.[name] = 'dbo' AND objects.[name] = 'CommandLog')

  BEGIN

    SET @ErrorMessage = 'The table CommandLog is missing. Download http://ola.hallengren.com/scripts/CommandLog.sql.' + CHAR(13) + CHAR(10) + ' '

    RAISERROR(@ErrorMessage,16,1) WITH NOWAIT

    SET @Error = @@ERROR

  END



  IF @Error <> 0

  BEGIN

    SET @ReturnCode = @Error

    GOTO Logging

  END



  ----------------------------------------------------------------------------------------------------

  --// Select databases                                                                           //--

  ----------------------------------------------------------------------------------------------------



  IF @Databases IS NULL OR @Databases = ''

  BEGIN

    SET @ErrorMessage = 'The value for parameter @Databases is not supported.' + CHAR(13) + CHAR(10) + ' '

    RAISERROR(@ErrorMessage,16,1) WITH NOWAIT

    SET @Error = @@ERROR

  END



  INSERT INTO @tmpDatabases (DatabaseName, Completed)

  SELECT DatabaseName AS DatabaseName,

         0 AS Completed

  FROM dbo.DatabaseSelect (@Databases)

  ORDER BY DatabaseName ASC



  IF @@ERROR <> 0

  BEGIN

    SET @ErrorMessage = 'Error selecting databases.' + CHAR(13) + CHAR(10) + ' '

    RAISERROR(@ErrorMessage,16,1) WITH NOWAIT

    SET @Error = @@ERROR

  END



  ----------------------------------------------------------------------------------------------------

  --// Select indexes                                                                             //--

  ----------------------------------------------------------------------------------------------------



  SET @CurrentIndexList = @Indexes



  WHILE CHARINDEX(', ',@CurrentIndexList) > 0 SET @CurrentIndexList = REPLACE(@CurrentIndexList,', ',',')

  WHILE CHARINDEX(' ,',@CurrentIndexList) > 0 SET @CurrentIndexList = REPLACE(@CurrentIndexList,' ,',',')

  WHILE CHARINDEX(',,',@CurrentIndexList) > 0 SET @CurrentIndexList = REPLACE(@CurrentIndexList,',,',',')



  IF RIGHT(@CurrentIndexList,1) = ',' SET @CurrentIndexList = LEFT(@CurrentIndexList,LEN(@CurrentIndexList) - 1)

  IF LEFT(@CurrentIndexList,1) = ',' SET @CurrentIndexList = RIGHT(@CurrentIndexList,LEN(@CurrentIndexList) - 1)



  SET @CurrentIndexList = LTRIM(RTRIM(@CurrentIndexList))



  WHILE LEN(@CurrentIndexList) > 0

  BEGIN

    SET @CurrentIndexPosition = CHARINDEX(',', @CurrentIndexList)

    IF @CurrentIndexPosition = 0

    BEGIN

      SET @CurrentIndexItem = @CurrentIndexList

      SET @CurrentIndexList = ''

    END

    ELSE

    BEGIN

      SET @CurrentIndexItem = LEFT(@CurrentIndexList, @CurrentIndexPosition - 1)

      SET @CurrentIndexList = RIGHT(@CurrentIndexList, LEN(@CurrentIndexList) - @CurrentIndexPosition)

    END;



    WITH IndexItem01 (IndexItem, Selected) AS (

    SELECT CASE WHEN @CurrentIndexItem LIKE '-%' THEN RIGHT(@CurrentIndexItem,LEN(@CurrentIndexItem) - 1) ELSE @CurrentIndexItem END AS IndexItem,

           CASE WHEN @CurrentIndexItem LIKE '-%' THEN 0 ELSE 1 END AS Selected),

    IndexItem02 (IndexItem, Selected) AS (

    SELECT CASE WHEN IndexItem = 'ALL_INDEXES' THEN '%.%.%.%' ELSE IndexItem END AS IndexItem,

           Selected

    FROM IndexItem01)

    INSERT INTO @SelectedIndexes (DatabaseName, SchemaName, ObjectName, IndexName, Selected)

    SELECT DatabaseName = CASE WHEN PARSENAME(IndexItem,4) IS NULL THEN PARSENAME(IndexItem,3) ELSE PARSENAME(IndexItem,4) END,

           SchemaName = CASE WHEN PARSENAME(IndexItem,4) IS NULL THEN PARSENAME(IndexItem,2) ELSE PARSENAME(IndexItem,3) END,

           ObjectName = CASE WHEN PARSENAME(IndexItem,4) IS NULL THEN PARSENAME(IndexItem,1) ELSE PARSENAME(IndexItem,2) END,

           IndexName = CASE WHEN PARSENAME(IndexItem,4) IS NULL THEN '%' ELSE PARSENAME(IndexItem,1) END,

           Selected

    FROM IndexItem02

  END



  IF EXISTS(SELECT * FROM @SelectedIndexes WHERE DatabaseName IS NULL OR SchemaName IS NULL OR ObjectName IS NULL OR IndexName IS NULL) OR (@Indexes IS NOT NULL AND NOT EXISTS(SELECT * FROM @SelectedIndexes))

  BEGIN

    SET @ErrorMessage = 'Error selecting indexes.' + CHAR(13) + CHAR(10) + ' '

    RAISERROR(@ErrorMessage,16,1) WITH NOWAIT

    SET @Error = @@ERROR

  END;



  ----------------------------------------------------------------------------------------------------

  --// Select actions                                                                             //--

  ----------------------------------------------------------------------------------------------------



  WITH FragmentationLow AS

  (

  SELECT CASE WHEN CHARINDEX(',', @FragmentationLow) = 0 THEN @FragmentationLow ELSE SUBSTRING(@FragmentationLow, 1, CHARINDEX(',', @FragmentationLow) - 1) END AS [Action],

         CASE WHEN CHARINDEX(',', @FragmentationLow) = 0 THEN '' ELSE SUBSTRING(@FragmentationLow, CHARINDEX(',', @FragmentationLow) + 1, LEN(@FragmentationLow)) END AS String,

         1 AS [Priority],

         CASE WHEN CHARINDEX(',', @FragmentationLow) = 0 THEN 0 ELSE 1 END [Continue]

  WHERE @FragmentationLow IS NOT NULL

  UNION ALL

  SELECT CASE WHEN CHARINDEX(',', String) = 0 THEN String ELSE SUBSTRING(String, 1, CHARINDEX(',', String) - 1) END AS [Action],

         CASE WHEN CHARINDEX(',', String) = 0 THEN '' ELSE SUBSTRING(String, CHARINDEX(',', String) + 1, LEN(String)) END AS String,

         [Priority] + 1  AS [Priority],

         CASE WHEN CHARINDEX(',', String) = 0 THEN 0 ELSE 1 END [Continue]

  FROM FragmentationLow

  WHERE [Continue] = 1

  ),

  FragmentationMedium AS

  (

  SELECT CASE WHEN CHARINDEX(',', @FragmentationMedium) = 0 THEN @FragmentationMedium ELSE SUBSTRING(@FragmentationMedium, 1, CHARINDEX(',', @FragmentationMedium) - 1) END AS [Action],

         CASE WHEN CHARINDEX(',', @FragmentationMedium) = 0 THEN '' ELSE SUBSTRING(@FragmentationMedium, CHARINDEX(',', @FragmentationMedium) + 1, LEN(@FragmentationMedium)) END AS String,

         1 AS [Priority],

         CASE WHEN CHARINDEX(',', @FragmentationMedium) = 0 THEN 0 ELSE 1 END [Continue]

  WHERE @FragmentationMedium IS NOT NULL

  UNION ALL

  SELECT CASE WHEN CHARINDEX(',', String) = 0 THEN String ELSE SUBSTRING(String, 1, CHARINDEX(',', String) - 1) END AS [Action],

         CASE WHEN CHARINDEX(',', String) = 0 THEN '' ELSE SUBSTRING(String, CHARINDEX(',', String) + 1, LEN(String)) END AS String,

         [Priority] + 1  AS [Priority],

         CASE WHEN CHARINDEX(',', String) = 0 THEN 0 ELSE 1 END [Continue]

  FROM FragmentationMedium

  WHERE [Continue] = 1

  ),

  FragmentationHigh AS

  (

  SELECT CASE WHEN CHARINDEX(',', @FragmentationHigh) = 0 THEN @FragmentationHigh ELSE SUBSTRING(@FragmentationHigh, 1, CHARINDEX(',', @FragmentationHigh) - 1) END AS [Action],

         CASE WHEN CHARINDEX(',', @FragmentationHigh) = 0 THEN '' ELSE SUBSTRING(@FragmentationHigh, CHARINDEX(',', @FragmentationHigh) + 1, LEN(@FragmentationHigh)) END AS String,

         1 AS [Priority],

         CASE WHEN CHARINDEX(',', @FragmentationHigh) = 0 THEN 0 ELSE 1 END [Continue]

  WHERE @FragmentationHigh IS NOT NULL

  UNION ALL

  SELECT CASE WHEN CHARINDEX(',', String) = 0 THEN String ELSE SUBSTRING(String, 1, CHARINDEX(',', String) - 1) END AS [Action],

         CASE WHEN CHARINDEX(',', String) = 0 THEN '' ELSE SUBSTRING(String, CHARINDEX(',', String) + 1, LEN(String)) END AS String,

         [Priority] + 1  AS [Priority],

         CASE WHEN CHARINDEX(',', String) = 0 THEN 0 ELSE 1 END [Continue]

  FROM FragmentationHigh

  WHERE [Continue] = 1

  )

  INSERT INTO @ActionsPreferred(FragmentationGroup, [Priority], [Action])

  SELECT 'Low' AS FragmentationGroup, [Priority], [Action]

  FROM FragmentationLow

  UNION

  SELECT 'Medium' AS FragmentationGroup, [Priority], [Action]

  FROM FragmentationMedium

  UNION

  SELECT 'High' AS FragmentationGroup, [Priority], [Action]

  FROM FragmentationHigh



  ----------------------------------------------------------------------------------------------------

  --// Check input parameters                                                                     //--

  ----------------------------------------------------------------------------------------------------



  IF EXISTS (SELECT [Action] FROM @ActionsPreferred WHERE FragmentationGroup = 'Low' AND [Action] NOT IN(SELECT * FROM @Actions))

  OR EXISTS(SELECT * FROM @ActionsPreferred WHERE FragmentationGroup = 'Low' GROUP BY [Action] HAVING COUNT(*) > 1)

  BEGIN

    SET @ErrorMessage = 'The value for parameter @FragmentationLow is not supported.' + CHAR(13) + CHAR(10) + ' '

    RAISERROR(@ErrorMessage,16,1) WITH NOWAIT

    SET @Error = @@ERROR

  END



  IF EXISTS (SELECT [Action] FROM @ActionsPreferred WHERE FragmentationGroup = 'Medium' AND [Action] NOT IN(SELECT * FROM @Actions))

  OR EXISTS(SELECT * FROM @ActionsPreferred WHERE FragmentationGroup = 'Medium' GROUP BY [Action] HAVING COUNT(*) > 1)

  BEGIN

    SET @ErrorMessage = 'The value for parameter @FragmentationMedium is not supported.' + CHAR(13) + CHAR(10) + ' '

    RAISERROR(@ErrorMessage,16,1) WITH NOWAIT

    SET @Error = @@ERROR

  END



  IF EXISTS (SELECT [Action] FROM @ActionsPreferred WHERE FragmentationGroup = 'High' AND [Action] NOT IN(SELECT * FROM @Actions))

  OR EXISTS(SELECT * FROM @ActionsPreferred WHERE FragmentationGroup = 'High' GROUP BY [Action] HAVING COUNT(*) > 1)

  BEGIN

    SET @ErrorMessage = 'The value for parameter @FragmentationHigh is not supported.' + CHAR(13) + CHAR(10) + ' '

    RAISERROR(@ErrorMessage,16,1) WITH NOWAIT

    SET @Error = @@ERROR

  END



  IF @FragmentationLevel1 <= 0 OR @FragmentationLevel1 >= 100 OR @FragmentationLevel1 >= @FragmentationLevel2 OR @FragmentationLevel1 IS NULL

  BEGIN

    SET @ErrorMessage = 'The value for parameter @FragmentationLevel1 is not supported.' + CHAR(13) + CHAR(10) + ' '

    RAISERROR(@ErrorMessage,16,1) WITH NOWAIT

    SET @Error = @@ERROR

  END



  IF @FragmentationLevel2 <= 0 OR @FragmentationLevel2 >= 100 OR @FragmentationLevel2 <= @FragmentationLevel1 OR @FragmentationLevel2 IS NULL

  BEGIN

    SET @ErrorMessage = 'The value for parameter @FragmentationLevel2 is not supported.' + CHAR(13) + CHAR(10) + ' '

    RAISERROR(@ErrorMessage,16,1) WITH NOWAIT

    SET @Error = @@ERROR

  END



  IF @PageCountLevel < 0 OR @PageCountLevel IS NULL

  BEGIN

    SET @ErrorMessage = 'The value for parameter @PageCountLevel is not supported.' + CHAR(13) + CHAR(10) + ' '

    RAISERROR(@ErrorMessage,16,1) WITH NOWAIT

    SET @Error = @@ERROR

  END



  IF @SortInTempdb NOT IN('Y','N') OR @SortInTempdb IS NULL

  BEGIN

    SET @ErrorMessage = 'The value for parameter @SortInTempdb is not supported.' + CHAR(13) + CHAR(10) + ' '

    RAISERROR(@ErrorMessage,16,1) WITH NOWAIT

    SET @Error = @@ERROR

  END



  IF @MaxDOP < 0 OR @MaxDOP > 64 OR @MaxDOP > (SELECT cpu_count FROM sys.dm_os_sys_info) OR (@MaxDOP > 1 AND SERVERPROPERTY('EngineEdition') <> 3)

  BEGIN

    SET @ErrorMessage = 'The value for parameter @MaxDOP is not supported.' + CHAR(13) + CHAR(10) + ' '

    RAISERROR(@ErrorMessage,16,1) WITH NOWAIT

    SET @Error = @@ERROR

  END



  IF @MaxDOP > 1 AND SERVERPROPERTY('EngineEdition') <> 3

  BEGIN

    SET @ErrorMessage = 'Parallel index operations are only supported in Enterprise, Developer and Datacenter Edition.' + CHAR(13) + CHAR(10) + ' '

    RAISERROR(@ErrorMessage,16,1) WITH NOWAIT

    SET @Error = @@ERROR

  END



  IF @FillFactor <= 0 OR @FillFactor > 100

  BEGIN

    SET @ErrorMessage = 'The value for parameter @FillFactor is not supported.' + CHAR(13) + CHAR(10) + ' '

    RAISERROR(@ErrorMessage,16,1) WITH NOWAIT

    SET @Error = @@ERROR

  END



  IF @PadIndex NOT IN('Y','N')

  BEGIN

    SET @ErrorMessage = 'The value for parameter @PadIndex is not supported.' + CHAR(13) + CHAR(10) + ' '

    RAISERROR(@ErrorMessage,16,1) WITH NOWAIT

    SET @Error = @@ERROR

  END



  IF @LOBCompaction NOT IN('Y','N') OR @LOBCompaction IS NULL

  BEGIN

    SET @ErrorMessage = 'The value for parameter @LOBCompaction is not supported.' + CHAR(13) + CHAR(10) + ' '

    RAISERROR(@ErrorMessage,16,1) WITH NOWAIT

    SET @Error = @@ERROR

  END



  IF @UpdateStatistics NOT IN('ALL','COLUMNS','INDEX')

  BEGIN

    SET @ErrorMessage = 'The value for parameter @UpdateStatistics is not supported.' + CHAR(13) + CHAR(10) + ' '

    RAISERROR(@ErrorMessage,16,1) WITH NOWAIT

    SET @Error = @@ERROR

  END



  IF @OnlyModifiedStatistics NOT IN('Y','N') OR @OnlyModifiedStatistics IS NULL

  BEGIN

    SET @ErrorMessage = 'The value for parameter @OnlyModifiedStatistics is not supported.' + CHAR(13) + CHAR(10) + ' '

    RAISERROR(@ErrorMessage,16,1) WITH NOWAIT

    SET @Error = @@ERROR

  END



  IF @StatisticsSample <= 0 OR @StatisticsSample  > 100

  BEGIN

    SET @ErrorMessage = 'The value for parameter @StatisticsSample is not supported.' + CHAR(13) + CHAR(10) + ' '

    RAISERROR(@ErrorMessage,16,1) WITH NOWAIT

    SET @Error = @@ERROR

  END



  IF @StatisticsResample NOT IN('Y','N') OR @StatisticsResample IS NULL OR (@StatisticsResample = 'Y' AND @StatisticsSample IS NOT NULL)

  BEGIN

    SET @ErrorMessage = 'The value for parameter @StatisticsResample is not supported.' + CHAR(13) + CHAR(10) + ' '

    RAISERROR(@ErrorMessage,16,1) WITH NOWAIT

    SET @Error = @@ERROR

  END



  IF @PartitionLevel NOT IN('Y','N') OR @PartitionLevel IS NULL OR (@PartitionLevel = 'Y' AND SERVERPROPERTY('EngineEdition') <> 3)

  BEGIN

    SET @ErrorMessage = 'The value for parameter @PartitionLevel is not supported.' + CHAR(13) + CHAR(10) + ' '

    RAISERROR(@ErrorMessage,16,1) WITH NOWAIT

    SET @Error = @@ERROR

  END



  IF @PartitionLevel = 'Y' AND SERVERPROPERTY('EngineEdition') <> 3

  BEGIN

    SET @ErrorMessage = 'Table partitioning is only supported in Enterprise, Developer and Datacenter Edition.' + CHAR(13) + CHAR(10) + ' '

    RAISERROR(@ErrorMessage,16,1) WITH NOWAIT

    SET @Error = @@ERROR

  END



  IF @TimeLimit < 0

  BEGIN

    SET @ErrorMessage = 'The value for parameter @TimeLimit is not supported.' + CHAR(13) + CHAR(10) + ' '

    RAISERROR(@ErrorMessage,16,1) WITH NOWAIT

    SET @Error = @@ERROR

  END



  IF @Delay < 0

  BEGIN

    SET @ErrorMessage = 'The value for parameter @Delay is not supported.' + CHAR(13) + CHAR(10) + ' '

    RAISERROR(@ErrorMessage,16,1) WITH NOWAIT

    SET @Error = @@ERROR

  END



  IF @LogToTable NOT IN('Y','N') OR @LogToTable IS NULL

  BEGIN

    SET @ErrorMessage = 'The value for parameter @LogToTable is not supported.' + CHAR(13) + CHAR(10) + ' '

    RAISERROR(@ErrorMessage,16,1) WITH NOWAIT

    SET @Error = @@ERROR

  END



  IF @Execute NOT IN('Y','N') OR @Execute IS NULL

  BEGIN

    SET @ErrorMessage = 'The value for parameter @Execute is not supported.' + CHAR(13) + CHAR(10) + ' '

    RAISERROR(@ErrorMessage,16,1) WITH NOWAIT

    SET @Error = @@ERROR

  END



  IF @Error <> 0

  BEGIN

    SET @ErrorMessage = 'The documentation is available on http://ola.hallengren.com/Documentation.html.' + CHAR(13) + CHAR(10) + ' '

    RAISERROR(@ErrorMessage,16,1) WITH NOWAIT

    SET @ReturnCode = @Error

    GOTO Logging

  END



  ----------------------------------------------------------------------------------------------------

  --// Execute commands                                                                           //--

  ----------------------------------------------------------------------------------------------------



  WHILE EXISTS (SELECT * FROM @tmpDatabases WHERE Completed = 0)

  BEGIN



    SELECT TOP 1 @CurrentID = ID,

                 @CurrentDatabaseName = DatabaseName

    FROM @tmpDatabases

    WHERE Completed = 0

    ORDER BY ID ASC



    SET @CurrentDatabaseID = DB_ID(@CurrentDatabaseName)



    IF EXISTS (SELECT * FROM sys.database_recovery_status WHERE database_id = @CurrentDatabaseID AND database_guid IS NOT NULL)

    BEGIN

      SET @CurrentIsDatabaseAccessible = 1

    END

    ELSE

    BEGIN

      SET @CurrentIsDatabaseAccessible = 0

    END



    SELECT @CurrentMirroringRole = mirroring_role_desc

    FROM sys.database_mirroring

    WHERE database_id = @CurrentDatabaseID



    -- Set database message

    SET @DatabaseMessage = 'DateTime: ' + CONVERT(nvarchar,GETDATE(),120) + CHAR(13) + CHAR(10)

    SET @DatabaseMessage = @DatabaseMessage + 'Database: ' + QUOTENAME(@CurrentDatabaseName) + CHAR(13) + CHAR(10)

    SET @DatabaseMessage = @DatabaseMessage + 'Status: ' + CAST(DATABASEPROPERTYEX(@CurrentDatabaseName,'Status') AS nvarchar) + CHAR(13) + CHAR(10)

    SET @DatabaseMessage = @DatabaseMessage + 'Mirroring role: ' + ISNULL(@CurrentMirroringRole,'N/A') + CHAR(13) + CHAR(10)

    SET @DatabaseMessage = @DatabaseMessage + 'Standby: ' + CASE WHEN DATABASEPROPERTYEX(@CurrentDatabaseName,'IsInStandBy') = 1 THEN 'Yes' ELSE 'No' END + CHAR(13) + CHAR(10)

    SET @DatabaseMessage = @DatabaseMessage + 'Updateability: ' + CAST(DATABASEPROPERTYEX(@CurrentDatabaseName,'Updateability') AS nvarchar) + CHAR(13) + CHAR(10)

    SET @DatabaseMessage = @DatabaseMessage + 'User access: ' + CAST(DATABASEPROPERTYEX(@CurrentDatabaseName,'UserAccess') AS nvarchar) + CHAR(13) + CHAR(10)

    SET @DatabaseMessage = @DatabaseMessage + 'Is accessible: ' + CASE WHEN @CurrentIsDatabaseAccessible = 1 THEN 'Yes' ELSE 'No' END + CHAR(13) + CHAR(10)

    SET @DatabaseMessage = @DatabaseMessage + 'Recovery model: ' + CAST(DATABASEPROPERTYEX(@CurrentDatabaseName,'Recovery') AS nvarchar) + CHAR(13) + CHAR(10) + ' '

    SET @DatabaseMessage = REPLACE(@DatabaseMessage,'%','%%')

    RAISERROR(@DatabaseMessage,10,1) WITH NOWAIT



    IF DATABASEPROPERTYEX(@CurrentDatabaseName,'Status') = 'ONLINE'

    AND NOT (DATABASEPROPERTYEX(@CurrentDatabaseName,'UserAccess') = 'SINGLE_USER' AND @CurrentIsDatabaseAccessible = 0)

    AND DATABASEPROPERTYEX(@CurrentDatabaseName,'Updateability') = 'READ_WRITE'

    BEGIN



      -- Select indexes in the current database

      IF EXISTS(SELECT * FROM @ActionsPreferred) OR @UpdateStatistics IS NOT NULL

      BEGIN

        SET @CurrentCommand01 = 'SELECT SchemaID, SchemaName, ObjectID, ObjectName, ObjectType, IndexID, IndexName, IndexType, StatisticsID, StatisticsName, PartitionID, PartitionNumber, PartitionCount, Selected, Completed FROM ('



        IF EXISTS(SELECT * FROM @ActionsPreferred) OR @UpdateStatistics IN('ALL','INDEX')

        BEGIN

          SET @CurrentCommand01 = @CurrentCommand01 + 'SELECT schemas.[schema_id] AS SchemaID, schemas.[name] AS SchemaName, objects.[object_id] AS ObjectID, objects.[name] AS ObjectName, RTRIM(objects.[type]) AS ObjectType, indexes.index_id AS IndexID, indexes.[name] AS IndexName, indexes.[type] AS IndexType, stats.stats_id AS StatisticsID, stats.name AS StatisticsName'

          IF @PartitionLevel = 'Y' SET @CurrentCommand01 = @CurrentCommand01 + ', partitions.partition_id AS PartitionID, partitions.partition_number AS PartitionNumber, IndexPartitions.partition_count AS PartitionCount'

          IF @PartitionLevel = 'N' SET @CurrentCommand01 = @CurrentCommand01 + ', NULL AS PartitionID, NULL AS PartitionNumber, NULL AS PartitionCount'

          SET @CurrentCommand01 = @CurrentCommand01 + ', 0 AS Selected, 0 AS Completed FROM ' + QUOTENAME(@CurrentDatabaseName) + '.sys.indexes indexes INNER JOIN ' + QUOTENAME(@CurrentDatabaseName) + '.sys.objects objects ON indexes.[object_id] = objects.[object_id] INNER JOIN ' + QUOTENAME(@CurrentDatabaseName) + '.sys.schemas schemas ON objects.[schema_id] = schemas.[schema_id] LEFT OUTER JOIN ' + QUOTENAME(@CurrentDatabaseName) + '.sys.stats stats ON indexes.[object_id] = stats.[object_id] AND indexes.[index_id] = stats.[stats_id]'

          IF @PartitionLevel = 'Y' SET @CurrentCommand01 = @CurrentCommand01 + ' LEFT OUTER JOIN ' + QUOTENAME(@CurrentDatabaseName) + '.sys.partitions partitions ON indexes.[object_id] = partitions.[object_id] AND indexes.index_id = partitions.index_id LEFT OUTER JOIN (SELECT partitions.[object_id], partitions.index_id, COUNT(*) AS partition_count FROM ' + QUOTENAME(@CurrentDatabaseName) + '.sys.partitions partitions GROUP BY partitions.[object_id], partitions.index_id) IndexPartitions ON partitions.[object_id] = IndexPartitions.[object_id] AND partitions.[index_id] = IndexPartitions.[index_id]'

          IF @PartitionLevel = 'Y' SET @CurrentCommand01 = @CurrentCommand01 + ' LEFT OUTER JOIN ' + QUOTENAME(@CurrentDatabaseName) + '.sys.dm_db_partition_stats dm_db_partition_stats ON indexes.[object_id] = dm_db_partition_stats.[object_id] AND indexes.[index_id] = dm_db_partition_stats.[index_id] AND partitions.partition_id = dm_db_partition_stats.partition_id'

          IF @PartitionLevel = 'N' SET @CurrentCommand01 = @CurrentCommand01 + ' LEFT OUTER JOIN (SELECT dm_db_partition_stats.[object_id], dm_db_partition_stats.[index_id], SUM(dm_db_partition_stats.in_row_data_page_count) AS in_row_data_page_count FROM ' + QUOTENAME(@CurrentDatabaseName) + '.sys.dm_db_partition_stats dm_db_partition_stats GROUP BY dm_db_partition_stats.[object_id], dm_db_partition_stats.[index_id]) dm_db_partition_stats ON indexes.[object_id] = dm_db_partition_stats.[object_id] AND indexes.[index_id] = dm_db_partition_stats.[index_id]'

          SET @CurrentCommand01 = @CurrentCommand01 + ' WHERE objects.[type] IN(''U'',''V'') AND objects.is_ms_shipped = 0 AND indexes.[type] IN(1,2,3,4) AND indexes.is_disabled = 0 AND indexes.is_hypothetical = 0'

          IF (@UpdateStatistics NOT IN('ALL','INDEX') OR @UpdateStatistics IS NULL) AND @PageCountLevel > 0 SET @CurrentCommand01 = @CurrentCommand01 + ' AND (dm_db_partition_stats.in_row_data_page_count >= @ParamPageCountLevel OR dm_db_partition_stats.in_row_data_page_count IS NULL)'

          IF NOT EXISTS(SELECT * FROM @ActionsPreferred) SET @CurrentCommand01 = @CurrentCommand01 + ' AND stats.stats_id IS NOT NULL'

        END



        IF (EXISTS(SELECT * FROM @ActionsPreferred) AND @UpdateStatistics = 'COLUMNS') OR @UpdateStatistics = 'ALL' SET @CurrentCommand01 = @CurrentCommand01 + ' UNION '



        IF @UpdateStatistics IN('ALL','COLUMNS') SET @CurrentCommand01 = @CurrentCommand01 + 'SELECT schemas.[schema_id] AS SchemaID, schemas.[name] AS SchemaName, objects.[object_id] AS ObjectID, objects.[name] AS ObjectName, RTRIM(objects.[type]) AS ObjectType, NULL AS IndexID, NULL AS IndexName, NULL AS IndexType, stats.stats_id AS StatisticsID, stats.name AS StatisticsName, NULL AS PartitionID, NULL AS PartitionNumber, NULL AS PartitionCount, 0 AS Selected, 0 AS Completed FROM ' + QUOTENAME(@CurrentDatabaseName) + '.sys.stats stats INNER JOIN ' + QUOTENAME(@CurrentDatabaseName) + '.sys.objects objects ON stats.[object_id] = objects.[object_id] INNER JOIN ' + QUOTENAME(@CurrentDatabaseName) + '.sys.schemas schemas ON objects.[schema_id] = schemas.[schema_id] WHERE objects.[type] IN(''U'',''V'') AND objects.is_ms_shipped = 0 AND NOT EXISTS(SELECT * FROM ' + QUOTENAME(@CurrentDatabaseName) + '.sys.indexes indexes WHERE indexes.[object_id] = stats.[object_id] AND indexes.index_id = stats.stats_id)'



        SET @CurrentCommand01 = @CurrentCommand01 + ') IndexesStatistics ORDER BY SchemaName ASC, ObjectName ASC'

        IF (EXISTS(SELECT * FROM @ActionsPreferred) AND @UpdateStatistics = 'COLUMNS') OR @UpdateStatistics = 'ALL' SET @CurrentCommand01 = @CurrentCommand01 + ', CASE WHEN IndexType IS NULL THEN 1 ELSE 0 END ASC'

        IF EXISTS(SELECT * FROM @ActionsPreferred) OR @UpdateStatistics IN('ALL','INDEX') SET @CurrentCommand01 = @CurrentCommand01 + ', IndexType ASC, IndexName ASC'

        IF @UpdateStatistics IN('ALL','COLUMNS') SET @CurrentCommand01 = @CurrentCommand01 + ', StatisticsName ASC'

        IF @PartitionLevel = 'Y' SET @CurrentCommand01 = @CurrentCommand01 + ', PartitionNumber ASC'



        INSERT INTO @tmpIndexesStatistics (SchemaID, SchemaName, ObjectID, ObjectName, ObjectType, IndexID, IndexName, IndexType, StatisticsID, StatisticsName, PartitionID, PartitionNumber, PartitionCount, Selected, Completed)

        EXECUTE sp_executesql @statement = @CurrentCommand01, @params = N'@ParamPageCountLevel int', @ParamPageCountLevel = @PageCountLevel

        SET @Error = @@ERROR

        IF @Error <> 0 SET @ReturnCode = @Error

        IF @Error = 1222

        BEGIN

          SET @ErrorMessage = 'The system tables are locked in the database ' + QUOTENAME(@CurrentDatabaseName) + '.' + CHAR(13) + CHAR(10) + ' '

          SET @ErrorMessage = REPLACE(@ErrorMessage,'%','%%')

          RAISERROR(@ErrorMessage,16,1) WITH NOWAIT

        END

      END



      IF @Indexes IS NULL

      BEGIN

        UPDATE tmpIndexesStatistics

        SET tmpIndexesStatistics.Selected = 1

        FROM @tmpIndexesStatistics tmpIndexesStatistics

      END

      ELSE

      BEGIN

        UPDATE tmpIndexesStatistics

        SET tmpIndexesStatistics.Selected = SelectedIndexes.Selected

        FROM @tmpIndexesStatistics tmpIndexesStatistics

        INNER JOIN @SelectedIndexes SelectedIndexes

        ON @CurrentDatabaseName LIKE REPLACE(SelectedIndexes.DatabaseName,'_','[_]') AND tmpIndexesStatistics.SchemaName LIKE REPLACE(SelectedIndexes.SchemaName,'_','[_]') AND tmpIndexesStatistics.ObjectName LIKE REPLACE(SelectedIndexes.ObjectName,'_','[_]') AND COALESCE(tmpIndexesStatistics.IndexName,tmpIndexesStatistics.StatisticsName) LIKE REPLACE(SelectedIndexes.IndexName,'_','[_]')

        WHERE SelectedIndexes.Selected = 1



        UPDATE tmpIndexesStatistics

        SET tmpIndexesStatistics.Selected = SelectedIndexes.Selected

        FROM @tmpIndexesStatistics tmpIndexesStatistics

        INNER JOIN @SelectedIndexes SelectedIndexes

        ON @CurrentDatabaseName LIKE REPLACE(SelectedIndexes.DatabaseName,'_','[_]') AND tmpIndexesStatistics.SchemaName LIKE REPLACE(SelectedIndexes.SchemaName,'_','[_]') AND tmpIndexesStatistics.ObjectName LIKE REPLACE(SelectedIndexes.ObjectName,'_','[_]') AND COALESCE(tmpIndexesStatistics.IndexName,tmpIndexesStatistics.StatisticsName) LIKE REPLACE(SelectedIndexes.IndexName,'_','[_]')

        WHERE SelectedIndexes.Selected = 0

      END



      WHILE EXISTS (SELECT * FROM @tmpIndexesStatistics WHERE Selected = 1 AND Completed = 0)

      BEGIN



        SELECT TOP 1 @CurrentIxID = IxID,

                     @CurrentSchemaID = SchemaID,

                     @CurrentSchemaName = SchemaName,

                     @CurrentObjectID = ObjectID,

                     @CurrentObjectName = ObjectName,

                     @CurrentObjectType = ObjectType,

                     @CurrentIndexID = IndexID,

                     @CurrentIndexName = IndexName,

                     @CurrentIndexType = IndexType,

                     @CurrentStatisticsID = StatisticsID,

                     @CurrentStatisticsName = StatisticsName,

                     @CurrentPartitionID = PartitionID,

                     @CurrentPartitionNumber = PartitionNumber,

                     @CurrentPartitionCount = PartitionCount

        FROM @tmpIndexesStatistics

        WHERE Selected = 1

        AND Completed = 0

        ORDER BY IxID ASC



        -- Is the index a partition?

        IF @CurrentPartitionNumber IS NULL OR @CurrentPartitionCount = 1 BEGIN SET @CurrentIsPartition = 0 END ELSE BEGIN SET @CurrentIsPartition = 1 END



        -- Does the index exist?

        IF @CurrentIndexID IS NOT NULL AND EXISTS(SELECT * FROM @ActionsPreferred)

        BEGIN

          IF @CurrentIsPartition = 0 SET @CurrentCommand02 = 'IF EXISTS(SELECT * FROM ' + QUOTENAME(@CurrentDatabaseName) + '.sys.indexes indexes INNER JOIN ' + QUOTENAME(@CurrentDatabaseName) + '.sys.objects objects ON indexes.[object_id] = objects.[object_id] INNER JOIN ' + QUOTENAME(@CurrentDatabaseName) + '.sys.schemas schemas ON objects.[schema_id] = schemas.[schema_id] WHERE objects.[type] IN(''U'',''V'') AND objects.is_ms_shipped = 0 AND indexes.[type] IN(1,2,3,4) AND indexes.is_disabled = 0 AND indexes.is_hypothetical = 0 AND schemas.[schema_id] = @ParamSchemaID AND schemas.[name] = @ParamSchemaName AND objects.[object_id] = @ParamObjectID AND objects.[name] = @ParamObjectName AND objects.[type] = @ParamObjectType AND indexes.index_id = @ParamIndexID AND indexes.[name] = @ParamIndexName AND indexes.[type] = @ParamIndexType) BEGIN SET @ParamIndexExists = 1 END'

          IF @CurrentIsPartition = 1 SET @CurrentCommand02 = 'IF EXISTS(SELECT * FROM ' + QUOTENAME(@CurrentDatabaseName) + '.sys.indexes indexes INNER JOIN ' + QUOTENAME(@CurrentDatabaseName) + '.sys.objects objects ON indexes.[object_id] = objects.[object_id] INNER JOIN ' + QUOTENAME(@CurrentDatabaseName) + '.sys.schemas schemas ON objects.[schema_id] = schemas.[schema_id] INNER JOIN ' + QUOTENAME(@CurrentDatabaseName) + '.sys.partitions partitions ON indexes.[object_id] = partitions.[object_id] AND indexes.index_id = partitions.index_id WHERE objects.[type] IN(''U'',''V'') AND objects.is_ms_shipped = 0 AND indexes.[type] IN(1,2,3,4) AND indexes.is_disabled = 0 AND indexes.is_hypothetical = 0 AND schemas.[schema_id] = @ParamSchemaID AND schemas.[name] = @ParamSchemaName AND objects.[object_id] = @ParamObjectID AND objects.[name] = @ParamObjectName AND objects.[type] = @ParamObjectType AND indexes.index_id = @ParamIndexID AND indexes.[name] = @ParamIndexName AND indexes.[type] = @ParamIndexType AND partitions.partition_id = @ParamPartitionID AND partitions.partition_number = @ParamPartitionNumber) BEGIN SET @ParamIndexExists = 1 END'



          EXECUTE sp_executesql @statement = @CurrentCommand02, @params = N'@ParamSchemaID int, @ParamSchemaName sysname, @ParamObjectID int, @ParamObjectName sysname, @ParamObjectType sysname, @ParamIndexID int, @ParamIndexName sysname, @ParamIndexType int, @ParamPartitionID bigint, @ParamPartitionNumber int, @ParamIndexExists bit OUTPUT', @ParamSchemaID = @CurrentSchemaID, @ParamSchemaName = @CurrentSchemaName, @ParamObjectID = @CurrentObjectID, @ParamObjectName = @CurrentObjectName, @ParamObjectType = @CurrentObjectType, @ParamIndexID = @CurrentIndexID, @ParamIndexName = @CurrentIndexName, @ParamIndexType = @CurrentIndexType, @ParamPartitionID = @CurrentPartitionID, @ParamPartitionNumber = @CurrentPartitionNumber, @ParamIndexExists = @CurrentIndexExists OUTPUT

          SET @Error = @@ERROR

          IF @Error = 0 AND @CurrentIndexExists IS NULL SET @CurrentIndexExists = 0

          IF @Error <> 0

          BEGIN

            SET @ReturnCode = @Error

            GOTO NoAction

          END

          IF @CurrentIndexExists = 0 GOTO NoAction

        END



        -- Does the statistics exist?

        IF @CurrentStatisticsID IS NOT NULL AND @UpdateStatistics IS NOT NULL

        BEGIN

          SET @CurrentCommand03 = 'IF EXISTS(SELECT * FROM ' + QUOTENAME(@CurrentDatabaseName) + '.sys.stats stats INNER JOIN ' + QUOTENAME(@CurrentDatabaseName) + '.sys.objects objects ON stats.[object_id] = objects.[object_id] INNER JOIN ' + QUOTENAME(@CurrentDatabaseName) + '.sys.schemas schemas ON objects.[schema_id] = schemas.[schema_id] WHERE objects.[type] IN(''U'',''V'') AND objects.is_ms_shipped = 0 AND schemas.[schema_id] = @ParamSchemaID AND schemas.[name] = @ParamSchemaName AND objects.[object_id] = @ParamObjectID AND objects.[name] = @ParamObjectName AND objects.[type] = @ParamObjectType AND stats.stats_id = @ParamStatisticsID AND stats.[name] = @ParamStatisticsName) BEGIN SET @ParamStatisticsExists = 1 END'



          EXECUTE sp_executesql @statement = @CurrentCommand03, @params = N'@ParamSchemaID int, @ParamSchemaName sysname, @ParamObjectID int, @ParamObjectName sysname, @ParamObjectType sysname, @ParamStatisticsID int, @ParamStatisticsName sysname, @ParamStatisticsExists bit OUTPUT', @ParamSchemaID = @CurrentSchemaID, @ParamSchemaName = @CurrentSchemaName, @ParamObjectID = @CurrentObjectID, @ParamObjectName = @CurrentObjectName, @ParamObjectType = @CurrentObjectType, @ParamStatisticsID = @CurrentStatisticsID, @ParamStatisticsName = @CurrentStatisticsName, @ParamStatisticsExists = @CurrentStatisticsExists OUTPUT

          SET @Error = @@ERROR

          IF @Error = 0 AND @CurrentStatisticsExists IS NULL SET @CurrentStatisticsExists = 0

          IF @Error <> 0

          BEGIN

            SET @ReturnCode = @Error

            GOTO NoAction

          END

          IF @CurrentStatisticsExists = 0 GOTO NoAction

        END



        -- Is one of the columns in the index an image, text or ntext data type?

        IF @CurrentIndexID IS NOT NULL AND @CurrentIndexType IN(1) AND EXISTS(SELECT * FROM @ActionsPreferred)

        BEGIN

          IF @CurrentIndexType = 1 SET @CurrentCommand11 = 'IF EXISTS(SELECT * FROM ' + QUOTENAME(@CurrentDatabaseName) + '.sys.columns columns INNER JOIN ' + QUOTENAME(@CurrentDatabaseName) + '.sys.types types ON columns.system_type_id = types.user_type_id WHERE columns.[object_id] = @ParamObjectID AND types.name IN(''image'',''text'',''ntext'')) BEGIN SET @ParamIsImageText = 1 END'



          EXECUTE sp_executesql @statement = @CurrentCommand11, @params = N'@ParamObjectID int, @ParamIndexID int, @ParamIsImageText bit OUTPUT', @ParamObjectID = @CurrentObjectID, @ParamIndexID = @CurrentIndexID, @ParamIsImageText = @CurrentIsImageText OUTPUT

          SET @Error = @@ERROR

          IF @Error = 0 AND @CurrentIsImageText IS NULL SET @CurrentIsImageText = 0

          IF @Error <> 0

          BEGIN

            SET @ReturnCode = @Error

            GOTO NoAction

          END

        END



        -- Is one of the columns in the index an xml, varchar(max), nvarchar(max), varbinary(max) or large CLR data type?

        IF @CurrentIndexID IS NOT NULL AND @CurrentIndexType IN(1,2) AND EXISTS(SELECT * FROM @ActionsPreferred)

        BEGIN

          IF @CurrentIndexType = 1 SET @CurrentCommand04 = 'IF EXISTS(SELECT * FROM ' + QUOTENAME(@CurrentDatabaseName) + '.sys.columns columns INNER JOIN ' + QUOTENAME(@CurrentDatabaseName) + '.sys.types types ON columns.system_type_id = types.user_type_id OR (columns.user_type_id = types.user_type_id AND types.is_assembly_type = 1) WHERE columns.[object_id] = @ParamObjectID AND (types.name IN(''xml'') OR (types.name IN(''varchar'',''nvarchar'',''varbinary'') AND columns.max_length = -1) OR (types.is_assembly_type = 1 AND columns.max_length = -1))) BEGIN SET @ParamIsNewLOB = 1 END'

          IF @CurrentIndexType = 2 SET @CurrentCommand04 = 'IF EXISTS(SELECT * FROM ' + QUOTENAME(@CurrentDatabaseName) + '.sys.index_columns index_columns INNER JOIN ' + QUOTENAME(@CurrentDatabaseName) + '.sys.columns columns ON index_columns.[object_id] = columns.[object_id] AND index_columns.column_id = columns.column_id INNER JOIN ' + QUOTENAME(@CurrentDatabaseName) + '.sys.types types ON columns.system_type_id = types.user_type_id OR (columns.user_type_id = types.user_type_id AND types.is_assembly_type = 1) WHERE index_columns.[object_id] = @ParamObjectID AND index_columns.index_id = @ParamIndexID AND (types.[name] IN(''xml'') OR (types.[name] IN(''varchar'',''nvarchar'',''varbinary'') AND columns.max_length = -1) OR (types.is_assembly_type = 1 AND columns.max_length = -1))) BEGIN SET @ParamIsNewLOB = 1 END'



          EXECUTE sp_executesql @statement = @CurrentCommand04, @params = N'@ParamObjectID int, @ParamIndexID int, @ParamIsNewLOB bit OUTPUT', @ParamObjectID = @CurrentObjectID, @ParamIndexID = @CurrentIndexID, @ParamIsNewLOB = @CurrentIsNewLOB OUTPUT

          SET @Error = @@ERROR

          IF @Error = 0 AND @CurrentIsNewLOB IS NULL SET @CurrentIsNewLOB = 0

          IF @Error <> 0

          BEGIN

            SET @ReturnCode = @Error

            GOTO NoAction

          END

        END



        -- Is one of the columns in the index a file stream column?

        IF @CurrentIndexID IS NOT NULL AND @CurrentIndexType IN(1) AND EXISTS(SELECT * FROM @ActionsPreferred)

        BEGIN

          IF @CurrentIndexType = 1 SET @CurrentCommand12 = 'IF EXISTS(SELECT * FROM ' + QUOTENAME(@CurrentDatabaseName) + '.sys.columns columns WHERE columns.[object_id] = @ParamObjectID  AND columns.is_filestream = 1) BEGIN SET @ParamIsFileStream = 1 END'



          EXECUTE sp_executesql @statement = @CurrentCommand12, @params = N'@ParamObjectID int, @ParamIndexID int, @ParamIsFileStream bit OUTPUT', @ParamObjectID = @CurrentObjectID, @ParamIndexID = @CurrentIndexID, @ParamIsFileStream = @CurrentIsFileStream OUTPUT

          SET @Error = @@ERROR

          IF @Error = 0 AND @CurrentIsFileStream IS NULL SET @CurrentIsFileStream = 0

          IF @Error <> 0

          BEGIN

            SET @ReturnCode = @Error

            GOTO NoAction

          END

        END



        -- Is Allow_Page_Locks set to On?

        IF @CurrentIndexID IS NOT NULL AND EXISTS(SELECT * FROM @ActionsPreferred)

        BEGIN

          SET @CurrentCommand05 = 'IF EXISTS(SELECT * FROM ' + QUOTENAME(@CurrentDatabaseName) + '.sys.indexes indexes WHERE indexes.[object_id] = @ParamObjectID AND indexes.[index_id] = @ParamIndexID AND indexes.[allow_page_locks] = 1) BEGIN SET @ParamAllowPageLocks = 1 END'



          EXECUTE sp_executesql @statement = @CurrentCommand05, @params = N'@ParamObjectID int, @ParamIndexID int, @ParamAllowPageLocks bit OUTPUT', @ParamObjectID = @CurrentObjectID, @ParamIndexID = @CurrentIndexID, @ParamAllowPageLocks = @CurrentAllowPageLocks OUTPUT

          SET @Error = @@ERROR

          IF @Error = 0 AND @CurrentAllowPageLocks IS NULL SET @CurrentAllowPageLocks = 0

          IF @Error <> 0

          BEGIN

            SET @ReturnCode = @Error

            GOTO NoAction

          END

        END



        -- Is No_Recompute set to On?

        IF @CurrentStatisticsID IS NOT NULL AND @UpdateStatistics IS NOT NULL

        BEGIN

          SET @CurrentCommand06 = 'IF EXISTS(SELECT * FROM ' + QUOTENAME(@CurrentDatabaseName) + '.sys.stats stats WHERE stats.[object_id] = @ParamObjectID AND stats.[stats_id] = @ParamStatisticsID AND stats.[no_recompute] = 1) BEGIN SET @ParamNoRecompute = 1 END'



          EXECUTE sp_executesql @statement = @CurrentCommand06, @params = N'@ParamObjectID int, @ParamStatisticsID int, @ParamNoRecompute bit OUTPUT', @ParamObjectID = @CurrentObjectID, @ParamStatisticsID = @CurrentStatisticsID, @ParamNoRecompute = @CurrentNoRecompute OUTPUT

          SET @Error = @@ERROR

          IF @Error = 0 AND @CurrentNoRecompute IS NULL SET @CurrentNoRecompute = 0

          IF @Error <> 0

          BEGIN

            SET @ReturnCode = @Error

            GOTO NoAction

          END

        END



        -- Has the data in the statistics been modified since the statistics was last updated?

        IF @CurrentStatisticsID IS NOT NULL AND @UpdateStatistics IS NOT NULL AND @OnlyModifiedStatistics = 'Y'

        BEGIN

          SET @CurrentCommand07 = 'IF EXISTS(SELECT * FROM ' + QUOTENAME(@CurrentDatabaseName) + '.sys.sysindexes sysindexes WHERE sysindexes.[id] = @ParamObjectID AND sysindexes.[indid] = @ParamStatisticsID AND sysindexes.[rowmodctr] <> 0) BEGIN SET @ParamStatisticsModified = 1 END'



          EXECUTE sp_executesql @statement = @CurrentCommand07, @params = N'@ParamObjectID int, @ParamStatisticsID int, @ParamStatisticsModified bit OUTPUT', @ParamObjectID = @CurrentObjectID, @ParamStatisticsID = @CurrentStatisticsID, @ParamStatisticsModified = @CurrentStatisticsModified OUTPUT

          SET @Error = @@ERROR

          IF @Error = 0 AND @CurrentStatisticsModified IS NULL SET @CurrentStatisticsModified = 0

          IF @Error <> 0

          BEGIN

            SET @ReturnCode = @Error

            GOTO NoAction

          END

        END



        -- Is the index on a read-only filegroup?

        IF @CurrentIndexID IS NOT NULL AND EXISTS(SELECT * FROM @ActionsPreferred)

        BEGIN

          SET @CurrentCommand08 = 'IF EXISTS(SELECT * FROM (SELECT filegroups.data_space_id FROM ' + QUOTENAME(@CurrentDatabaseName) + '.sys.indexes indexes INNER JOIN ' + QUOTENAME(@CurrentDatabaseName) + '.sys.destination_data_spaces destination_data_spaces ON indexes.data_space_id = destination_data_spaces.partition_scheme_id INNER JOIN ' + QUOTENAME(@CurrentDatabaseName) + '.sys.filegroups filegroups ON destination_data_spaces.data_space_id = filegroups.data_space_id WHERE filegroups.is_read_only = 1 AND indexes.[object_id] = @ParamObjectID AND indexes.[index_id] = @ParamIndexID'

          IF @CurrentIsPartition = 1 SET @CurrentCommand08 = @CurrentCommand08 + ' AND destination_data_spaces.destination_id = @ParamPartitionNumber'

          SET @CurrentCommand08 = @CurrentCommand08 + ' UNION SELECT filegroups.data_space_id FROM ' + QUOTENAME(@CurrentDatabaseName) + '.sys.indexes indexes INNER JOIN ' + QUOTENAME(@CurrentDatabaseName) + '.sys.filegroups filegroups ON indexes.data_space_id = filegroups.data_space_id WHERE filegroups.is_read_only = 1 AND indexes.[object_id] = @ParamObjectID AND indexes.[index_id] = @ParamIndexID'

          IF @CurrentIndexType = 1 SET @CurrentCommand08 = @CurrentCommand08 + ' UNION SELECT filegroups.data_space_id FROM ' + QUOTENAME(@CurrentDatabaseName) + '.sys.tables tables INNER JOIN ' + QUOTENAME(@CurrentDatabaseName) + '.sys.filegroups filegroups ON tables.lob_data_space_id = filegroups.data_space_id WHERE filegroups.is_read_only = 1 AND tables.[object_id] = @ParamObjectID'

          SET @CurrentCommand08 = @CurrentCommand08 + ') ReadOnlyFileGroups) BEGIN SET @ParamOnReadOnlyFileGroup = 1 END'



          EXECUTE sp_executesql @statement = @CurrentCommand08, @params = N'@ParamObjectID int, @ParamIndexID int, @ParamPartitionNumber int, @ParamOnReadOnlyFileGroup bit OUTPUT', @ParamObjectID = @CurrentObjectID, @ParamIndexID = @CurrentIndexID, @ParamPartitionNumber = @CurrentPartitionNumber, @ParamOnReadOnlyFileGroup = @CurrentOnReadOnlyFileGroup OUTPUT

          SET @Error = @@ERROR

          IF @Error = 0 AND @CurrentOnReadOnlyFileGroup IS NULL SET @CurrentOnReadOnlyFileGroup = 0

          IF @Error <> 0

          BEGIN

            SET @ReturnCode = @Error

            GOTO NoAction

          END

        END



        -- Is the index fragmented?

        IF @CurrentIndexID IS NOT NULL

        AND EXISTS(SELECT * FROM @ActionsPreferred)

        AND (EXISTS(SELECT [Priority], [Action], COUNT(*) FROM @ActionsPreferred GROUP BY [Priority], [Action] HAVING COUNT(*) <> 3) OR @PageCountLevel > 0)

        BEGIN

          SELECT @CurrentFragmentationLevel = MAX(avg_fragmentation_in_percent),

                 @CurrentPageCount = SUM(page_count)

          FROM sys.dm_db_index_physical_stats(@CurrentDatabaseID, @CurrentObjectID, @CurrentIndexID, @CurrentPartitionNumber, 'LIMITED')

          WHERE alloc_unit_type_desc = 'IN_ROW_DATA'

          AND index_level = 0

          SET @Error = @@ERROR

          IF @Error = 1222

          BEGIN

            SET @ErrorMessage = 'The dynamic management view sys.dm_db_index_physical_stats is locked on the index ' + QUOTENAME(@CurrentSchemaName) + '.' + QUOTENAME(@CurrentObjectName) + '.' + QUOTENAME(@CurrentIndexName) + '.' + CHAR(13) + CHAR(10) + ' '

            SET @ErrorMessage = REPLACE(@ErrorMessage,'%','%%')

            RAISERROR(@ErrorMessage,16,1) WITH NOWAIT

            END

          IF @Error <> 0

          BEGIN

            SET @ReturnCode = @Error

            GOTO NoAction

          END

        END



        -- Select fragmentation group

        IF @CurrentIndexID IS NOT NULL AND EXISTS(SELECT * FROM @ActionsPreferred)

        BEGIN

          SET @CurrentFragmentationGroup = CASE

          WHEN @CurrentFragmentationLevel >= @FragmentationLevel2 THEN 'High'

          WHEN @CurrentFragmentationLevel >= @FragmentationLevel1 AND @CurrentFragmentationLevel < @FragmentationLevel2 THEN 'Medium'

          WHEN @CurrentFragmentationLevel < @FragmentationLevel1 THEN 'Low'

          END

        END



        -- Which actions are allowed?

        IF @CurrentIndexID IS NOT NULL AND EXISTS(SELECT * FROM @ActionsPreferred)

        BEGIN

          IF @CurrentOnReadOnlyFileGroup = 0 AND @CurrentAllowPageLocks = 1

          BEGIN

            INSERT INTO @CurrentActionsAllowed ([Action])

            VALUES ('INDEX_REORGANIZE')

          END

          IF @CurrentOnReadOnlyFileGroup = 0

          BEGIN

            INSERT INTO @CurrentActionsAllowed ([Action])

            VALUES ('INDEX_REBUILD_OFFLINE')

          END

          IF @CurrentOnReadOnlyFileGroup = 0

          AND @CurrentIsPartition = 0

          AND ((@CurrentIndexType = 1 AND @CurrentIsImageText = 0 AND @CurrentIsNewLOB = 0)

          OR (@CurrentIndexType = 2 AND @CurrentIsNewLOB = 0)

          OR (@CurrentIndexType = 1 AND @CurrentIsImageText = 0 AND @CurrentIsFileStream = 0 AND @Version >= 11)

          OR (@CurrentIndexType = 2 AND @Version >= 11))

          AND SERVERPROPERTY('EngineEdition') = 3

          BEGIN

            INSERT INTO @CurrentActionsAllowed ([Action])

            VALUES ('INDEX_REBUILD_ONLINE')

          END

        END



        -- Decide action

        IF @CurrentIndexID IS NOT NULL

        AND EXISTS(SELECT * FROM @ActionsPreferred)

        AND (@CurrentPageCount >= @PageCountLevel OR @PageCountLevel = 0)

        BEGIN

          IF EXISTS(SELECT [Priority], [Action], COUNT(*) FROM @ActionsPreferred GROUP BY [Priority], [Action] HAVING COUNT(*) <> 3)

          BEGIN

            SELECT @CurrentAction = [Action]

            FROM @ActionsPreferred

            WHERE FragmentationGroup = @CurrentFragmentationGroup

            AND [Priority] = (SELECT MIN([Priority])

                              FROM @ActionsPreferred

                              WHERE FragmentationGroup = @CurrentFragmentationGroup

                              AND [Action] IN (SELECT [Action] FROM @CurrentActionsAllowed))

          END

          ELSE

          BEGIN

            SELECT @CurrentAction = [Action]

            FROM @ActionsPreferred

            WHERE [Priority] = (SELECT MIN([Priority])

                                FROM @ActionsPreferred

                                WHERE [Action] IN (SELECT [Action] FROM @CurrentActionsAllowed))

          END

        END



        -- Workaround for a bug in SQL Server 2005, SQL Server 2008 and SQL Server 2008 R2, http://support.microsoft.com/kb/2292737

        IF @CurrentIndexID IS NOT NULL

        BEGIN

          SET @CurrentMaxDOP = @MaxDOP

          IF @Version < 11 AND @CurrentAction = 'INDEX_REBUILD_ONLINE' AND @CurrentAllowPageLocks = 0

          BEGIN

            SET @CurrentMaxDOP = 1

          END

        END



        -- Update statistics?

        IF @CurrentStatisticsID IS NOT NULL

        AND (@UpdateStatistics = 'ALL' OR (@UpdateStatistics = 'INDEX' AND @CurrentIndexID IS NOT NULL) OR (@UpdateStatistics = 'COLUMNS' AND @CurrentIndexID IS NULL))

        AND (@CurrentStatisticsModified = 1 OR @OnlyModifiedStatistics = 'N')

        AND ((@CurrentIsPartition = 0 AND (@CurrentAction NOT IN('INDEX_REBUILD_ONLINE','INDEX_REBUILD_OFFLINE') OR @CurrentAction IS NULL)) OR (@CurrentIsPartition = 1 AND @CurrentPartitionNumber = @CurrentPartitionCount))

        BEGIN

          SET @CurrentUpdateStatistics = 'Y'

        END

        ELSE

        BEGIN

          SET @CurrentUpdateStatistics = 'N'

        END



        -- Create comment

        IF @CurrentIndexID IS NOT NULL

        BEGIN

          SET @CurrentComment = 'ObjectType: ' + CASE WHEN @CurrentObjectType = 'U' THEN 'Table' WHEN @CurrentObjectType = 'V' THEN 'View' ELSE 'N/A' END + ', '

          SET @CurrentComment = @CurrentComment + 'IndexType: ' + CASE WHEN @CurrentIndexType = 1 THEN 'Clustered' WHEN @CurrentIndexType = 2 THEN 'NonClustered' WHEN @CurrentIndexType = 3 THEN 'XML' WHEN @CurrentIndexType = 4 THEN 'Spatial' ELSE 'N/A' END + ', '

          SET @CurrentComment = @CurrentComment + 'ImageText: ' + CASE WHEN @CurrentIsImageText = 1 THEN 'Yes' WHEN @CurrentIsImageText = 0 THEN 'No' ELSE 'N/A' END + ', '

          SET @CurrentComment = @CurrentComment + 'NewLOB: ' + CASE WHEN @CurrentIsNewLOB = 1 THEN 'Yes' WHEN @CurrentIsNewLOB = 0 THEN 'No' ELSE 'N/A' END + ', '

          SET @CurrentComment = @CurrentComment + 'FileStream: ' + CASE WHEN @CurrentIsFileStream = 1 THEN 'Yes' WHEN @CurrentIsFileStream = 0 THEN 'No' ELSE 'N/A' END + ', '

          SET @CurrentComment = @CurrentComment + 'AllowPageLocks: ' + CASE WHEN @CurrentAllowPageLocks = 1 THEN 'Yes' WHEN @CurrentAllowPageLocks = 0 THEN 'No' ELSE 'N/A' END + ', '

          SET @CurrentComment = @CurrentComment + 'PageCount: ' + ISNULL(CAST(@CurrentPageCount AS nvarchar),'N/A') + ', '

          SET @CurrentComment = @CurrentComment + 'Fragmentation: ' + ISNULL(CAST(@CurrentFragmentationLevel AS nvarchar),'N/A')

        END



        IF @CurrentIndexID IS NOT NULL AND (@CurrentPageCount IS NOT NULL OR @CurrentFragmentationLevel IS NOT NULL)

        BEGIN

        SET @CurrentExtendedInfo = (SELECT *

                                    FROM (SELECT CAST(@CurrentPageCount AS nvarchar) AS [PageCount],

                                                 CAST(@CurrentFragmentationLevel AS nvarchar) AS Fragmentation

                                    ) ExtendedInfo FOR XML AUTO, ELEMENTS)

        END



        -- Check time limit

        IF GETDATE() >= DATEADD(ss,@TimeLimit,@StartTime)

        BEGIN

          SET @Execute = 'N'

        END



        IF @CurrentIndexID IS NOT NULL AND @CurrentAction IS NOT NULL

        BEGIN

          SET @CurrentCommandType09 = 'ALTER_INDEX'



          SET @CurrentCommand09 = 'ALTER INDEX ' + QUOTENAME(@CurrentIndexName) + ' ON ' + QUOTENAME(@CurrentDatabaseName) + '.' + QUOTENAME(@CurrentSchemaName) + '.' + QUOTENAME(@CurrentObjectName)



          IF @CurrentAction IN('INDEX_REBUILD_ONLINE','INDEX_REBUILD_OFFLINE')

          BEGIN

            SET @CurrentCommand09 = @CurrentCommand09 + ' REBUILD'

            IF @CurrentIsPartition = 1 SET @CurrentCommand09 = @CurrentCommand09 + ' PARTITION = ' + CAST(@CurrentPartitionNumber AS nvarchar)

            SET @CurrentCommand09 = @CurrentCommand09 + ' WITH ('

            IF @SortInTempdb = 'Y' SET @CurrentCommand09 = @CurrentCommand09 + 'SORT_IN_TEMPDB = ON'

            IF @SortInTempdb = 'N' SET @CurrentCommand09 = @CurrentCommand09 + 'SORT_IN_TEMPDB = OFF'

            IF @CurrentAction = 'INDEX_REBUILD_ONLINE' AND @CurrentIsPartition = 0 SET @CurrentCommand09 = @CurrentCommand09 + ', ONLINE = ON'

            IF @CurrentAction = 'INDEX_REBUILD_OFFLINE' AND @CurrentIsPartition = 0 SET @CurrentCommand09 = @CurrentCommand09 + ', ONLINE = OFF'

            IF @CurrentMaxDOP IS NOT NULL SET @CurrentCommand09 = @CurrentCommand09 + ', MAXDOP = ' + CAST(@CurrentMaxDOP AS nvarchar)

            IF @FillFactor IS NOT NULL AND @CurrentIsPartition = 0 SET @CurrentCommand09 = @CurrentCommand09 + ', FILLFACTOR = ' + CAST(@FillFactor AS nvarchar)

            IF @PadIndex = 'Y' AND @CurrentIsPartition = 0 SET @CurrentCommand09 = @CurrentCommand09 + ', PAD_INDEX = ON'

            IF @PadIndex = 'N' AND @CurrentIsPartition = 0 SET @CurrentCommand09 = @CurrentCommand09 + ', PAD_INDEX = OFF'

            SET @CurrentCommand09 = @CurrentCommand09 + ')'

          END



          IF @CurrentAction IN('INDEX_REORGANIZE')

          BEGIN

            SET @CurrentCommand09 = @CurrentCommand09 + ' REORGANIZE'

            IF @CurrentIsPartition = 1 SET @CurrentCommand09 = @CurrentCommand09 + ' PARTITION = ' + CAST(@CurrentPartitionNumber AS nvarchar)

            SET @CurrentCommand09 = @CurrentCommand09 + ' WITH ('

            IF @LOBCompaction = 'Y' SET @CurrentCommand09 = @CurrentCommand09 + 'LOB_COMPACTION = ON'

            IF @LOBCompaction = 'N' SET @CurrentCommand09 = @CurrentCommand09 + 'LOB_COMPACTION = OFF'

            SET @CurrentCommand09 = @CurrentCommand09 + ')'

          END



          EXECUTE @CurrentCommandOutput09 = [dbo].[CommandExecute] @Command = @CurrentCommand09, @CommandType = @CurrentCommandType09, @Mode = 2, @Comment = @CurrentComment, @DatabaseName = @CurrentDatabaseName, @SchemaName = @CurrentSchemaName, @ObjectName = @CurrentObjectName, @ObjectType = @CurrentObjectType, @IndexName = @CurrentIndexName, @IndexType = @CurrentIndexType, @PartitionNumber = @CurrentPartitionNumber, @ExtendedInfo = @CurrentExtendedInfo, @LogToTable = @LogToTable, @Execute = @Execute

          SET @Error = @@ERROR

          IF @Error <> 0 SET @CurrentCommandOutput09 = @Error

          IF @CurrentCommandOutput09 <> 0 SET @ReturnCode = @CurrentCommandOutput09



          IF @Delay > 0

          BEGIN

            SET @CurrentDelay = DATEADD(ss,@Delay,'1900-01-01')

            WAITFOR DELAY @CurrentDelay

          END

        END



        IF @CurrentStatisticsID IS NOT NULL AND @CurrentUpdateStatistics = 'Y'

        BEGIN

          SET @CurrentCommandType10 = 'UPDATE_STATISTICS'



          SET @CurrentCommand10 = 'UPDATE STATISTICS ' + QUOTENAME(@CurrentDatabaseName) + '.' + QUOTENAME(@CurrentSchemaName) + '.' + QUOTENAME(@CurrentObjectName) + ' ' + QUOTENAME(@CurrentStatisticsName)

          IF @StatisticsSample IS NOT NULL OR @StatisticsResample = 'Y' OR @CurrentNoRecompute = 1 SET @CurrentCommand10 = @CurrentCommand10 + ' WITH'

          IF @StatisticsSample = 100 SET @CurrentCommand10 = @CurrentCommand10 + ' FULLSCAN'

          IF @StatisticsSample IS NOT NULL AND @StatisticsSample <> 100 SET @CurrentCommand10 = @CurrentCommand10 + ' SAMPLE ' + CAST(@StatisticsSample AS nvarchar) + ' PERCENT'

          IF @StatisticsResample = 'Y' SET @CurrentCommand10 = @CurrentCommand10 + ' RESAMPLE'

          IF (@StatisticsSample IS NOT NULL OR @StatisticsResample = 'Y') AND @CurrentNoRecompute = 1 SET @CurrentCommand10 = @CurrentCommand10 + ','

          IF @CurrentNoRecompute = 1 SET @CurrentCommand10 = @CurrentCommand10 + ' NORECOMPUTE'



          EXECUTE @CurrentCommandOutput10 = [dbo].[CommandExecute] @Command = @CurrentCommand10, @CommandType = @CurrentCommandType10, @Mode = 2, @DatabaseName = @CurrentDatabaseName, @SchemaName = @CurrentSchemaName, @ObjectName = @CurrentObjectName, @ObjectType = @CurrentObjectType, @IndexName = @CurrentIndexName, @IndexType = @CurrentIndexType, @StatisticsName = @CurrentStatisticsName, @LogToTable = @LogToTable, @Execute = @Execute

          SET @Error = @@ERROR

          IF @Error <> 0 SET @CurrentCommandOutput10 = @Error

          IF @CurrentCommandOutput10 <> 0 SET @ReturnCode = @CurrentCommandOutput10

        END



        NoAction:



        -- Update that the index is completed

        UPDATE @tmpIndexesStatistics

        SET Completed = 1

        WHERE Selected = 1

        AND Completed = 0

        AND IxID = @CurrentIxID



        -- Clear variables

        SET @CurrentCommand02 = NULL

        SET @CurrentCommand03 = NULL

        SET @CurrentCommand04 = NULL

        SET @CurrentCommand05 = NULL

        SET @CurrentCommand06 = NULL

        SET @CurrentCommand07 = NULL

        SET @CurrentCommand08 = NULL

        SET @CurrentCommand09 = NULL

        SET @CurrentCommand10 = NULL



        SET @CurrentCommandOutput09 = NULL

        SET @CurrentCommandOutput10 = NULL



        SET @CurrentCommandType09 = NULL

        SET @CurrentCommandType10 = NULL



        SET @CurrentIxID = NULL

        SET @CurrentSchemaID = NULL

        SET @CurrentSchemaName = NULL

        SET @CurrentObjectID = NULL

        SET @CurrentObjectName = NULL

        SET @CurrentObjectType = NULL

        SET @CurrentIndexID = NULL

        SET @CurrentIndexName = NULL

        SET @CurrentIndexType = NULL

        SET @CurrentStatisticsID = NULL

        SET @CurrentStatisticsName = NULL

        SET @CurrentPartitionID = NULL

        SET @CurrentPartitionNumber = NULL

        SET @CurrentPartitionCount = NULL

        SET @CurrentIsPartition = NULL

        SET @CurrentIndexExists = NULL

        SET @CurrentStatisticsExists = NULL

        SET @CurrentIsImageText = NULL

        SET @CurrentIsNewLOB = NULL

        SET @CurrentIsFileStream = NULL

        SET @CurrentAllowPageLocks = NULL

        SET @CurrentNoRecompute = NULL

        SET @CurrentStatisticsModified = NULL

        SET @CurrentOnReadOnlyFileGroup = NULL

        SET @CurrentFragmentationLevel = NULL

        SET @CurrentPageCount = NULL

        SET @CurrentFragmentationGroup = NULL

        SET @CurrentAction = NULL

        SET @CurrentMaxDOP = NULL

        SET @CurrentUpdateStatistics = NULL

        SET @CurrentComment = NULL

        SET @CurrentExtendedInfo = NULL



        DELETE FROM @CurrentActionsAllowed



      END



    END



    -- Update that the database is completed

    UPDATE @tmpDatabases

    SET Completed = 1

    WHERE ID = @CurrentID



    -- Clear variables

    SET @CurrentID = NULL

    SET @CurrentDatabaseID = NULL

    SET @CurrentDatabaseName = NULL

    SET @CurrentIsDatabaseAccessible = NULL

    SET @CurrentMirroringRole = NULL



    SET @CurrentCommand01 = NULL



    DELETE FROM @tmpIndexesStatistics



  END



  ----------------------------------------------------------------------------------------------------

  --// Log completing information                                                                 //--

  ----------------------------------------------------------------------------------------------------



  Logging:

  SET @EndMessage = 'DateTime: ' + CONVERT(nvarchar,GETDATE(),120)

  SET @EndMessage = REPLACE(@EndMessage,'%','%%')

  RAISERROR(@EndMessage,10,1) WITH NOWAIT



  IF @ReturnCode <> 0

  BEGIN

    RETURN @ReturnCode

  END



  ----------------------------------------------------------------------------------------------------



END

GO