Configuring custom database maintenance

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[CommandExecute]’) AND type in (N’P’, N’PC’))
BEGIN
EXEC dbo.sp_executesql @statement = N’CREATE PROCEDURE [dbo].[CommandExecute] AS’
END
GO
ALTER PROCEDURE [dbo].[CommandExecute]

@DatabaseContext nvarchar(max),
@Command nvarchar(max),
@CommandType nvarchar(max),
@Mode int,
@Comment nvarchar(max) = NULL,
@DatabaseName nvarchar(max) = NULL,
@SchemaName nvarchar(max) = NULL,
@ObjectName nvarchar(max) = NULL,
@ObjectType nvarchar(max) = NULL,
@IndexName nvarchar(max) = NULL,
@IndexType int = NULL,
@StatisticsName nvarchar(max) = NULL,
@PartitionNumber int = NULL,
@ExtendedInfo xml = NULL,
@LockMessageSeverity int = 16,
@ExecuteAsUser nvarchar(max) = NULL,
@LogToTable nvarchar(max),
@Execute nvarchar(max)

AS

BEGIN

—————————————————————————————————-
–// Source: https://ola.hallengren.com //–
–// License: https://ola.hallengren.com/license.html //–
–// GitHub: https://github.com/olahallengren/sql-server-maintenance-solution //–
–// Version: 2022-12-03 17:23:44 //–
—————————————————————————————————-

SET NOCOUNT ON

DECLARE @StartMessage nvarchar(max)
DECLARE @EndMessage nvarchar(max)
DECLARE @ErrorMessage nvarchar(max)
DECLARE @ErrorMessageOriginal nvarchar(max)
DECLARE @Severity int

DECLARE @Errors TABLE (ID int IDENTITY PRIMARY KEY,
[Message] nvarchar(max) NOT NULL,
Severity int NOT NULL,
[State] int)

DECLARE @CurrentMessage nvarchar(max)
DECLARE @CurrentSeverity int
DECLARE @CurrentState int

DECLARE @sp_executesql nvarchar(max) = QUOTENAME(@DatabaseContext) + ‘.sys.sp_executesql’

DECLARE @StartTime datetime2
DECLARE @EndTime datetime2

DECLARE @ID int

DECLARE @Error int = 0
DECLARE @ReturnCode int = 0

DECLARE @EmptyLine nvarchar(max) = CHAR(9)

DECLARE @RevertCommand nvarchar(max)

—————————————————————————————————-
–// Check core requirements //–
—————————————————————————————————-

IF NOT (SELECT [compatibility_level] FROM sys.databases WHERE database_id = DB_ID()) >= 90
BEGIN
INSERT INTO @Errors ([Message], Severity, [State])
SELECT ‘The database ‘ + QUOTENAME(DB_NAME(DB_ID())) + ‘ has to be in compatibility level 90 or higher.’, 16, 1
END

IF NOT (SELECT uses_ansi_nulls FROM sys.sql_modules WHERE [object_id] = @@PROCID) = 1
BEGIN
INSERT INTO @Errors ([Message], Severity, [State])
SELECT ‘ANSI_NULLS has to be set to ON for the stored procedure.’, 16, 1
END

IF NOT (SELECT uses_quoted_identifier FROM sys.sql_modules WHERE [object_id] = @@PROCID) = 1
BEGIN
INSERT INTO @Errors ([Message], Severity, [State])
SELECT ‘QUOTED_IDENTIFIER has to be set to ON for the stored procedure.’, 16, 1
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
INSERT INTO @Errors ([Message], Severity, [State])
SELECT ‘The table CommandLog is missing. Download https://ola.hallengren.com/scripts/CommandLog.sql.’, 16, 1
END

—————————————————————————————————-
–// Check input parameters //–
—————————————————————————————————-

IF @DatabaseContext IS NULL OR NOT EXISTS (SELECT * FROM sys.databases WHERE name = @DatabaseContext)
BEGIN
INSERT INTO @Errors ([Message], Severity, [State])
SELECT ‘The value for the parameter @DatabaseContext is not supported.’, 16, 1
END

IF @Command IS NULL OR @Command = ”
BEGIN
INSERT INTO @Errors ([Message], Severity, [State])
SELECT ‘The value for the parameter @Command is not supported.’, 16, 1
END

IF @CommandType IS NULL OR @CommandType = ” OR LEN(@CommandType) > 60
BEGIN
INSERT INTO @Errors ([Message], Severity, [State])
SELECT ‘The value for the parameter @CommandType is not supported.’, 16, 1
END

IF @Mode NOT IN(1,2) OR @Mode IS NULL
BEGIN
INSERT INTO @Errors ([Message], Severity, [State])
SELECT ‘The value for the parameter @Mode is not supported.’, 16, 1
END

IF @LockMessageSeverity NOT IN(10,16) OR @LockMessageSeverity IS NULL
BEGIN
INSERT INTO @Errors ([Message], Severity, [State])
SELECT ‘The value for the parameter @LockMessageSeverity is not supported.’, 16, 1
END

IF LEN(@ExecuteAsUser) > 128
BEGIN
INSERT INTO @Errors ([Message], Severity, [State])
SELECT ‘The value for the parameter @ExecuteAsUser is not supported.’, 16, 1
END

IF @LogToTable NOT IN(‘Y’,’N’) OR @LogToTable IS NULL
BEGIN
INSERT INTO @Errors ([Message], Severity, [State])
SELECT ‘The value for the parameter @LogToTable is not supported.’, 16, 1
END

IF @Execute NOT IN(‘Y’,’N’) OR @Execute IS NULL
BEGIN
INSERT INTO @Errors ([Message], Severity, [State])
SELECT ‘The value for the parameter @Execute is not supported.’, 16, 1
END

—————————————————————————————————-
–// Raise errors //–
—————————————————————————————————-

DECLARE ErrorCursor CURSOR FAST_FORWARD FOR SELECT [Message], Severity, [State] FROM @Errors ORDER BY [ID] ASC

OPEN ErrorCursor

FETCH ErrorCursor INTO @CurrentMessage, @CurrentSeverity, @CurrentState

WHILE @@FETCH_STATUS = 0
BEGIN
RAISERROR(‘%s’, @CurrentSeverity, @CurrentState, @CurrentMessage) WITH NOWAIT
RAISERROR(@EmptyLine, 10, 1) WITH NOWAIT

FETCH NEXT FROM ErrorCursor INTO @CurrentMessage, @CurrentSeverity, @CurrentState
END

CLOSE ErrorCursor

DEALLOCATE ErrorCursor

IF EXISTS (SELECT * FROM @Errors WHERE Severity >= 16)
BEGIN
SET @ReturnCode = 50000
GOTO ReturnCode
END

—————————————————————————————————-
–// Execute as user //–
—————————————————————————————————-

IF @ExecuteAsUser IS NOT NULL
BEGIN
SET @Command = ‘EXECUTE AS USER = ”’ + REPLACE(@ExecuteAsUser,””,”””) + ”’; ‘ + @Command + ‘; REVERT;’

SET @RevertCommand = ‘REVERT’
END

—————————————————————————————————-
–// Log initial information //–
—————————————————————————————————-

SET @StartTime = SYSDATETIME()

SET @StartMessage = ‘Date and time: ‘ + CONVERT(nvarchar,@StartTime,120)
RAISERROR(‘%s’,10,1,@StartMessage) WITH NOWAIT

SET @StartMessage = ‘Database context: ‘ + QUOTENAME(@DatabaseContext)
RAISERROR(‘%s’,10,1,@StartMessage) WITH NOWAIT

SET @StartMessage = ‘Command: ‘ + @Command
RAISERROR(‘%s’,10,1,@StartMessage) WITH NOWAIT

IF @Comment IS NOT NULL
BEGIN
SET @StartMessage = ‘Comment: ‘ + @Comment
RAISERROR(‘%s’,10,1,@StartMessage) WITH NOWAIT
END

IF @LogToTable = ‘Y’
BEGIN
INSERT INTO dbo.CommandLog (DatabaseName, SchemaName, ObjectName, ObjectType, IndexName, IndexType, StatisticsName, PartitionNumber, ExtendedInfo, CommandType, Command, StartTime)
VALUES (@DatabaseName, @SchemaName, @ObjectName, @ObjectType, @IndexName, @IndexType, @StatisticsName, @PartitionNumber, @ExtendedInfo, @CommandType, @Command, @StartTime)
END

SET @ID = SCOPE_IDENTITY()

—————————————————————————————————-
–// Execute command //–
—————————————————————————————————-

IF @Mode = 1 AND @Execute = ‘Y’
BEGIN
EXECUTE @sp_executesql @stmt = @Command
SET @Error = @@ERROR
SET @ReturnCode = @Error
END

IF @Mode = 2 AND @Execute = ‘Y’
BEGIN
BEGIN TRY
EXECUTE @sp_executesql @stmt = @Command
END TRY
BEGIN CATCH
SET @Error = ERROR_NUMBER()
SET @ErrorMessageOriginal = ERROR_MESSAGE()

SET @ErrorMessage = ‘Msg ‘ + CAST(ERROR_NUMBER() AS nvarchar) + ‘, ‘ + ISNULL(ERROR_MESSAGE(),”)
SET @Severity = CASE WHEN ERROR_NUMBER() IN(1205,1222) THEN @LockMessageSeverity ELSE 16 END
RAISERROR(‘%s’,@Severity,1,@ErrorMessage) WITH NOWAIT

IF NOT (ERROR_NUMBER() IN(1205,1222) AND @LockMessageSeverity = 10)
BEGIN
SET @ReturnCode = ERROR_NUMBER()
END

IF @ExecuteAsUser IS NOT NULL
BEGIN
EXECUTE @sp_executesql @RevertCommand
END
END CATCH
END

—————————————————————————————————-
–// Log completing information //–
—————————————————————————————————-

SET @EndTime = SYSDATETIME()

SET @EndMessage = ‘Outcome: ‘ + CASE WHEN @Execute = ‘N’ THEN ‘Not Executed’ WHEN @Error = 0 THEN ‘Succeeded’ ELSE ‘Failed’ END
RAISERROR(‘%s’,10,1,@EndMessage) WITH NOWAIT

SET @EndMessage = ‘Duration: ‘ + CASE WHEN (DATEDIFF(SECOND,@StartTime,@EndTime) / (24 * 3600)) > 0 THEN CAST((DATEDIFF(SECOND,@StartTime,@EndTime) / (24 * 3600)) AS nvarchar) + ‘.’ ELSE ” END + CONVERT(nvarchar,DATEADD(SECOND,DATEDIFF(SECOND,@StartTime,@EndTime),’1900-01-01′),108)
RAISERROR(‘%s’,10,1,@EndMessage) WITH NOWAIT

SET @EndMessage = ‘Date and time: ‘ + CONVERT(nvarchar,@EndTime,120)
RAISERROR(‘%s’,10,1,@EndMessage) WITH NOWAIT

RAISERROR(@EmptyLine,10,1) WITH NOWAIT

IF @LogToTable = ‘Y’
BEGIN
UPDATE dbo.CommandLog
SET EndTime = @EndTime,
ErrorNumber = CASE WHEN @Execute = ‘N’ THEN NULL ELSE @Error END,
ErrorMessage = @ErrorMessageOriginal
WHERE ID = @ID
END

ReturnCode:
IF @ReturnCode 0
BEGIN
RETURN @ReturnCode
END

—————————————————————————————————-

END
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[IndexOptimize]’) AND type in (N’P’, N’PC’))
BEGIN
EXEC dbo.sp_executesql @statement = N’CREATE PROCEDURE [dbo].[IndexOptimize] AS’
END
GO
ALTER PROCEDURE [dbo].[IndexOptimize]

@Databases nvarchar(max) = NULL,
@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,
@MinNumberOfPages int = 1000,
@MaxNumberOfPages int = NULL,
@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’,
@StatisticsModificationLevel int = NULL,
@StatisticsSample int = NULL,
@StatisticsResample nvarchar(max) = ‘N’,
@PartitionLevel nvarchar(max) = ‘Y’,
@MSShippedObjects nvarchar(max) = ‘N’,
@Indexes nvarchar(max) = NULL,
@TimeLimit int = NULL,
@Delay int = NULL,
@WaitAtLowPriorityMaxDuration int = NULL,
@WaitAtLowPriorityAbortAfterWait nvarchar(max) = NULL,
@Resumable nvarchar(max) = ‘N’,
@AvailabilityGroups nvarchar(max) = NULL,
@LockTimeout int = NULL,
@LockMessageSeverity int = 16,
@StringDelimiter nvarchar(max) = ‘,’,
@DatabaseOrder nvarchar(max) = NULL,
@DatabasesInParallel nvarchar(max) = ‘N’,
@ExecuteAsUser nvarchar(max) = NULL,
@LogToTable nvarchar(max) = ‘N’,
@Execute nvarchar(max) = ‘Y’

AS

BEGIN

—————————————————————————————————-
–// Source: https://ola.hallengren.com //–
–// License: https://ola.hallengren.com/license.html //–
–// GitHub: https://github.com/olahallengren/sql-server-maintenance-solution //–
–// Version: 2022-12-03 17:23:44 //–
—————————————————————————————————-

SET NOCOUNT ON

SET ARITHABORT ON

SET NUMERIC_ROUNDABORT OFF

DECLARE @StartMessage nvarchar(max)
DECLARE @EndMessage nvarchar(max)
DECLARE @DatabaseMessage nvarchar(max)
DECLARE @ErrorMessage nvarchar(max)
DECLARE @Severity int

DECLARE @StartTime datetime2 = SYSDATETIME()
DECLARE @SchemaName nvarchar(max) = OBJECT_SCHEMA_NAME(@@PROCID)
DECLARE @ObjectName nvarchar(max) = OBJECT_NAME(@@PROCID)
DECLARE @VersionTimestamp nvarchar(max) = SUBSTRING(OBJECT_DEFINITION(@@PROCID),CHARINDEX(‘–// Version: ‘,OBJECT_DEFINITION(@@PROCID)) + LEN(‘–// Version: ‘) + 1, 19)
DECLARE @Parameters nvarchar(max)

DECLARE @HostPlatform nvarchar(max)

DECLARE @PartitionLevelStatistics bit

DECLARE @QueueID int
DECLARE @QueueStartTime datetime2

DECLARE @CurrentDBID int
DECLARE @CurrentDatabaseName nvarchar(max)

DECLARE @CurrentDatabase_sp_executesql nvarchar(max)

DECLARE @CurrentExecuteAsUserExists bit
DECLARE @CurrentUserAccess nvarchar(max)
DECLARE @CurrentIsReadOnly bit
DECLARE @CurrentDatabaseState nvarchar(max)
DECLARE @CurrentInStandby bit
DECLARE @CurrentRecoveryModel nvarchar(max)

DECLARE @CurrentIsDatabaseAccessible bit
DECLARE @CurrentReplicaID uniqueidentifier
DECLARE @CurrentAvailabilityGroupID uniqueidentifier
DECLARE @CurrentAvailabilityGroup nvarchar(max)
DECLARE @CurrentAvailabilityGroupRole nvarchar(max)
DECLARE @CurrentDatabaseMirroringRole nvarchar(max)

DECLARE @CurrentDatabaseContext nvarchar(max)
DECLARE @CurrentCommand nvarchar(max)
DECLARE @CurrentCommandOutput int
DECLARE @CurrentCommandType nvarchar(max)
DECLARE @CurrentComment nvarchar(max)
DECLARE @CurrentExtendedInfo xml

DECLARE @Errors TABLE (ID int IDENTITY PRIMARY KEY,
[Message] nvarchar(max) NOT NULL,
Severity int NOT NULL,
[State] int)

DECLARE @CurrentMessage nvarchar(max)
DECLARE @CurrentSeverity int
DECLARE @CurrentState int

DECLARE @CurrentIxID int
DECLARE @CurrentIxOrder int
DECLARE @CurrentSchemaID int
DECLARE @CurrentSchemaName nvarchar(max)
DECLARE @CurrentObjectID int
DECLARE @CurrentObjectName nvarchar(max)
DECLARE @CurrentObjectType nvarchar(max)
DECLARE @CurrentIsMemoryOptimized bit
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 @CurrentIsColumnStore bit
DECLARE @CurrentIsComputed bit
DECLARE @CurrentIsTimestamp bit
DECLARE @CurrentAllowPageLocks bit
DECLARE @CurrentNoRecompute bit
DECLARE @CurrentIsIncremental bit
DECLARE @CurrentRowCount bigint
DECLARE @CurrentModificationCounter bigint
DECLARE @CurrentOnReadOnlyFileGroup bit
DECLARE @CurrentResumableIndexOperation bit
DECLARE @CurrentFragmentationLevel float
DECLARE @CurrentPageCount bigint
DECLARE @CurrentFragmentationGroup nvarchar(max)
DECLARE @CurrentAction nvarchar(max)
DECLARE @CurrentMaxDOP int
DECLARE @CurrentUpdateStatistics nvarchar(max)
DECLARE @CurrentStatisticsSample int
DECLARE @CurrentStatisticsResample nvarchar(max)
DECLARE @CurrentDelay datetime

DECLARE @tmpDatabases TABLE (ID int IDENTITY,
DatabaseName nvarchar(max),
DatabaseType nvarchar(max),
AvailabilityGroup bit,
StartPosition int,
DatabaseSize bigint,
[Order] int,
Selected bit,
Completed bit,
PRIMARY KEY(Selected, Completed, [Order], ID))

DECLARE @tmpAvailabilityGroups TABLE (ID int IDENTITY PRIMARY KEY,
AvailabilityGroupName nvarchar(max),
StartPosition int,
Selected bit)

DECLARE @tmpDatabasesAvailabilityGroups TABLE (DatabaseName nvarchar(max),
AvailabilityGroupName nvarchar(max))

DECLARE @tmpIndexesStatistics TABLE (ID int IDENTITY,
SchemaID int,
SchemaName nvarchar(max),
ObjectID int,
ObjectName nvarchar(max),
ObjectType nvarchar(max),
IsMemoryOptimized bit,
IndexID int,
IndexName nvarchar(max),
IndexType int,
AllowPageLocks bit,
IsImageText bit,
IsNewLOB bit,
IsFileStream bit,
IsColumnStore bit,
IsComputed bit,
IsTimestamp bit,
OnReadOnlyFileGroup bit,
ResumableIndexOperation bit,
StatisticsID int,
StatisticsName nvarchar(max),
[NoRecompute] bit,
IsIncremental bit,
PartitionID bigint,
PartitionNumber int,
PartitionCount int,
StartPosition int,
[Order] int,
Selected bit,
Completed bit,
PRIMARY KEY(Selected, Completed, [Order], ID))

DECLARE @SelectedDatabases TABLE (DatabaseName nvarchar(max),
DatabaseType nvarchar(max),
AvailabilityGroup nvarchar(max),
StartPosition int,
Selected bit)

DECLARE @SelectedAvailabilityGroups TABLE (AvailabilityGroupName nvarchar(max),
StartPosition int,
Selected bit)

DECLARE @SelectedIndexes TABLE (DatabaseName nvarchar(max),
SchemaName nvarchar(max),
ObjectName nvarchar(max),
IndexName nvarchar(max),
StartPosition int,
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 @CurrentAlterIndexWithClauseArguments TABLE (ID int IDENTITY,
Argument nvarchar(max),
Added bit DEFAULT 0)

DECLARE @CurrentAlterIndexArgumentID int
DECLARE @CurrentAlterIndexArgument nvarchar(max)
DECLARE @CurrentAlterIndexWithClause nvarchar(max)

DECLARE @CurrentUpdateStatisticsWithClauseArguments TABLE (ID int IDENTITY,
Argument nvarchar(max),
Added bit DEFAULT 0)

DECLARE @CurrentUpdateStatisticsArgumentID int
DECLARE @CurrentUpdateStatisticsArgument nvarchar(max)
DECLARE @CurrentUpdateStatisticsWithClause nvarchar(max)

DECLARE @Error int = 0
DECLARE @ReturnCode int = 0

DECLARE @EmptyLine nvarchar(max) = CHAR(9)

DECLARE @Version numeric(18,10) = 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))

IF @Version >= 14
BEGIN
SELECT @HostPlatform = host_platform
FROM sys.dm_os_host_info
END
ELSE
BEGIN
SET @HostPlatform = ‘Windows’
END

DECLARE @AmazonRDS bit = CASE WHEN DB_ID(‘rdsadmin’) IS NOT NULL AND SUSER_SNAME(0x01) = ‘rdsa’ THEN 1 ELSE 0 END

—————————————————————————————————-
–// Log initial information //–
—————————————————————————————————-

SET @Parameters = ‘@Databases = ‘ + ISNULL(”” + REPLACE(@Databases,””,”””) + ””,’NULL’)
SET @Parameters += ‘, @FragmentationLow = ‘ + ISNULL(”” + REPLACE(@FragmentationLow,””,”””) + ””,’NULL’)
SET @Parameters += ‘, @FragmentationMedium = ‘ + ISNULL(”” + REPLACE(@FragmentationMedium,””,”””) + ””,’NULL’)
SET @Parameters += ‘, @FragmentationHigh = ‘ + ISNULL(”” + REPLACE(@FragmentationHigh,””,”””) + ””,’NULL’)
SET @Parameters += ‘, @FragmentationLevel1 = ‘ + ISNULL(CAST(@FragmentationLevel1 AS nvarchar),’NULL’)
SET @Parameters += ‘, @FragmentationLevel2 = ‘ + ISNULL(CAST(@FragmentationLevel2 AS nvarchar),’NULL’)
SET @Parameters += ‘, @MinNumberOfPages = ‘ + ISNULL(CAST(@MinNumberOfPages AS nvarchar),’NULL’)
SET @Parameters += ‘, @MaxNumberOfPages = ‘ + ISNULL(CAST(@MaxNumberOfPages AS nvarchar),’NULL’)
SET @Parameters += ‘, @SortInTempdb = ‘ + ISNULL(”” + REPLACE(@SortInTempdb,””,”””) + ””,’NULL’)
SET @Parameters += ‘, @MaxDOP = ‘ + ISNULL(CAST(@MaxDOP AS nvarchar),’NULL’)
SET @Parameters += ‘, @FillFactor = ‘ + ISNULL(CAST(@FillFactor AS nvarchar),’NULL’)
SET @Parameters += ‘, @PadIndex = ‘ + ISNULL(”” + REPLACE(@PadIndex,””,”””) + ””,’NULL’)
SET @Parameters += ‘, @LOBCompaction = ‘ + ISNULL(”” + REPLACE(@LOBCompaction,””,”””) + ””,’NULL’)
SET @Parameters += ‘, @UpdateStatistics = ‘ + ISNULL(”” + REPLACE(@UpdateStatistics,””,”””) + ””,’NULL’)
SET @Parameters += ‘, @OnlyModifiedStatistics = ‘ + ISNULL(”” + REPLACE(@OnlyModifiedStatistics,””,”””) + ””,’NULL’)
SET @Parameters += ‘, @StatisticsModificationLevel = ‘ + ISNULL(CAST(@StatisticsModificationLevel AS nvarchar),’NULL’)
SET @Parameters += ‘, @StatisticsSample = ‘ + ISNULL(CAST(@StatisticsSample AS nvarchar),’NULL’)
SET @Parameters += ‘, @StatisticsResample = ‘ + ISNULL(”” + REPLACE(@StatisticsResample,””,”””) + ””,’NULL’)
SET @Parameters += ‘, @PartitionLevel = ‘ + ISNULL(”” + REPLACE(@PartitionLevel,””,”””) + ””,’NULL’)
SET @Parameters += ‘, @MSShippedObjects = ‘ + ISNULL(”” + REPLACE(@MSShippedObjects,””,”””) + ””,’NULL’)
SET @Parameters += ‘, @Indexes = ‘ + ISNULL(”” + REPLACE(@Indexes,””,”””) + ””,’NULL’)
SET @Parameters += ‘, @TimeLimit = ‘ + ISNULL(CAST(@TimeLimit AS nvarchar),’NULL’)
SET @Parameters += ‘, @Delay = ‘ + ISNULL(CAST(@Delay AS nvarchar),’NULL’)
SET @Parameters += ‘, @WaitAtLowPriorityMaxDuration = ‘ + ISNULL(CAST(@WaitAtLowPriorityMaxDuration AS nvarchar),’NULL’)
SET @Parameters += ‘, @WaitAtLowPriorityAbortAfterWait = ‘ + ISNULL(”” + REPLACE(@WaitAtLowPriorityAbortAfterWait,””,”””) + ””,’NULL’)
SET @Parameters += ‘, @Resumable = ‘ + ISNULL(”” + REPLACE(@Resumable,””,”””) + ””,’NULL’)
SET @Parameters += ‘, @AvailabilityGroups = ‘ + ISNULL(”” + REPLACE(@AvailabilityGroups,””,”””) + ””,’NULL’)
SET @Parameters += ‘, @LockTimeout = ‘ + ISNULL(CAST(@LockTimeout AS nvarchar),’NULL’)
SET @Parameters += ‘, @LockMessageSeverity = ‘ + ISNULL(CAST(@LockMessageSeverity AS nvarchar),’NULL’)
SET @Parameters += ‘, @StringDelimiter = ‘ + ISNULL(”” + REPLACE(@StringDelimiter,””,”””) + ””,’NULL’)
SET @Parameters += ‘, @DatabaseOrder = ‘ + ISNULL(”” + REPLACE(@DatabaseOrder,””,”””) + ””,’NULL’)
SET @Parameters += ‘, @DatabasesInParallel = ‘ + ISNULL(”” + REPLACE(@DatabasesInParallel,””,”””) + ””,’NULL’)
SET @Parameters += ‘, @ExecuteAsUser = ‘ + ISNULL(”” + REPLACE(@ExecuteAsUser,””,”””) + ””,’NULL’)
SET @Parameters += ‘, @LogToTable = ‘ + ISNULL(”” + REPLACE(@LogToTable,””,”””) + ””,’NULL’)
SET @Parameters += ‘, @Execute = ‘ + ISNULL(”” + REPLACE(@Execute,””,”””) + ””,’NULL’)

SET @StartMessage = ‘Date and time: ‘ + CONVERT(nvarchar,@StartTime,120)
RAISERROR(‘%s’,10,1,@StartMessage) WITH NOWAIT

SET @StartMessage = ‘Server: ‘ + CAST(SERVERPROPERTY(‘ServerName’) AS nvarchar(max))
RAISERROR(‘%s’,10,1,@StartMessage) WITH NOWAIT

SET @StartMessage = ‘Version: ‘ + CAST(SERVERPROPERTY(‘ProductVersion’) AS nvarchar(max))
RAISERROR(‘%s’,10,1,@StartMessage) WITH NOWAIT

SET @StartMessage = ‘Edition: ‘ + CAST(SERVERPROPERTY(‘Edition’) AS nvarchar(max))
RAISERROR(‘%s’,10,1,@StartMessage) WITH NOWAIT

SET @StartMessage = ‘Platform: ‘ + @HostPlatform
RAISERROR(‘%s’,10,1,@StartMessage) WITH NOWAIT

SET @StartMessage = ‘Procedure: ‘ + QUOTENAME(DB_NAME(DB_ID())) + ‘.’ + QUOTENAME(@SchemaName) + ‘.’ + QUOTENAME(@ObjectName)
RAISERROR(‘%s’,10,1,@StartMessage) WITH NOWAIT

SET @StartMessage = ‘Parameters: ‘ + @Parameters
RAISERROR(‘%s’,10,1,@StartMessage) WITH NOWAIT

SET @StartMessage = ‘Version: ‘ + @VersionTimestamp
RAISERROR(‘%s’,10,1,@StartMessage) WITH NOWAIT

SET @StartMessage = ‘Source: https://ola.hallengren.com’
RAISERROR(‘%s’,10,1,@StartMessage) WITH NOWAIT

RAISERROR(@EmptyLine,10,1) WITH NOWAIT

—————————————————————————————————-
–// Check core requirements //–
—————————————————————————————————-

IF NOT (SELECT [compatibility_level] FROM sys.databases WHERE database_id = DB_ID()) >= 90
BEGIN
INSERT INTO @Errors ([Message], Severity, [State])
SELECT ‘The database ‘ + QUOTENAME(DB_NAME(DB_ID())) + ‘ has to be in compatibility level 90 or higher.’, 16, 1
END

IF NOT (SELECT uses_ansi_nulls FROM sys.sql_modules WHERE [object_id] = @@PROCID) = 1
BEGIN
INSERT INTO @Errors ([Message], Severity, [State])
SELECT ‘ANSI_NULLS has to be set to ON for the stored procedure.’, 16, 1
END

IF NOT (SELECT uses_quoted_identifier FROM sys.sql_modules WHERE [object_id] = @@PROCID) = 1
BEGIN
INSERT INTO @Errors ([Message], Severity, [State])
SELECT ‘QUOTED_IDENTIFIER has to be set to ON for the stored procedure.’, 16, 1
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
INSERT INTO @Errors ([Message], Severity, [State])
SELECT ‘The stored procedure CommandExecute is missing. Download https://ola.hallengren.com/scripts/CommandExecute.sql.’, 16, 1
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 ‘%@DatabaseContext%’)
BEGIN
INSERT INTO @Errors ([Message], Severity, [State])
SELECT ‘The stored procedure CommandExecute needs to be updated. Download https://ola.hallengren.com/scripts/CommandExecute.sql.’, 16, 1
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
INSERT INTO @Errors ([Message], Severity, [State])
SELECT ‘The table CommandLog is missing. Download https://ola.hallengren.com/scripts/CommandLog.sql.’, 16, 1
END

IF @DatabasesInParallel = ‘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] = ‘Queue’)
BEGIN
INSERT INTO @Errors ([Message], Severity, [State])
SELECT ‘The table Queue is missing. Download https://ola.hallengren.com/scripts/Queue.sql.’, 16, 1
END

IF @DatabasesInParallel = ‘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] = ‘QueueDatabase’)
BEGIN
INSERT INTO @Errors ([Message], Severity, [State])
SELECT ‘The table QueueDatabase is missing. Download https://ola.hallengren.com/scripts/QueueDatabase.sql.’, 16, 1
END

IF @@TRANCOUNT 0
BEGIN
INSERT INTO @Errors ([Message], Severity, [State])
SELECT ‘The transaction count is not 0.’, 16, 1
END

—————————————————————————————————-
–// Select databases //–
—————————————————————————————————-

SET @Databases = REPLACE(@Databases, CHAR(10), ”)
SET @Databases = REPLACE(@Databases, CHAR(13), ”)

WHILE CHARINDEX(@StringDelimiter + ‘ ‘, @Databases) > 0 SET @Databases = REPLACE(@Databases, @StringDelimiter + ‘ ‘, @StringDelimiter)
WHILE CHARINDEX(‘ ‘ + @StringDelimiter, @Databases) > 0 SET @Databases = REPLACE(@Databases, ‘ ‘ + @StringDelimiter, @StringDelimiter)

SET @Databases = LTRIM(RTRIM(@Databases));

WITH Databases1 (StartPosition, EndPosition, DatabaseItem) AS
(
SELECT 1 AS StartPosition,
ISNULL(NULLIF(CHARINDEX(@StringDelimiter, @Databases, 1), 0), LEN(@Databases) + 1) AS EndPosition,
SUBSTRING(@Databases, 1, ISNULL(NULLIF(CHARINDEX(@StringDelimiter, @Databases, 1), 0), LEN(@Databases) + 1) – 1) AS DatabaseItem
WHERE @Databases IS NOT NULL
UNION ALL
SELECT CAST(EndPosition AS int) + 1 AS StartPosition,
ISNULL(NULLIF(CHARINDEX(@StringDelimiter, @Databases, EndPosition + 1), 0), LEN(@Databases) + 1) AS EndPosition,
SUBSTRING(@Databases, EndPosition + 1, ISNULL(NULLIF(CHARINDEX(@StringDelimiter, @Databases, EndPosition + 1), 0), LEN(@Databases) + 1) – EndPosition – 1) AS DatabaseItem
FROM Databases1
WHERE EndPosition = 11 AND SERVERPROPERTY(‘IsHadrEnabled’) = 1
BEGIN
INSERT INTO @tmpAvailabilityGroups (AvailabilityGroupName, Selected)
SELECT name AS AvailabilityGroupName,
0 AS Selected
FROM sys.availability_groups

INSERT INTO @tmpDatabasesAvailabilityGroups (DatabaseName, AvailabilityGroupName)
SELECT databases.name,
availability_groups.name
FROM sys.databases databases
INNER JOIN sys.availability_replicas availability_replicas ON databases.replica_id = availability_replicas.replica_id
INNER JOIN sys.availability_groups availability_groups ON availability_replicas.group_id = availability_groups.group_id
END

INSERT INTO @tmpDatabases (DatabaseName, DatabaseType, AvailabilityGroup, [Order], Selected, Completed)
SELECT [name] AS DatabaseName,
CASE WHEN name IN(‘master’,’msdb’,’model’) OR is_distributor = 1 THEN ‘S’ ELSE ‘U’ END AS DatabaseType,
NULL AS AvailabilityGroup,
0 AS [Order],
0 AS Selected,
0 AS Completed
FROM sys.databases
WHERE [name] ‘tempdb’
AND source_database_id IS NULL
ORDER BY [name] ASC

UPDATE tmpDatabases
SET AvailabilityGroup = CASE WHEN EXISTS (SELECT * FROM @tmpDatabasesAvailabilityGroups WHERE DatabaseName = tmpDatabases.DatabaseName) THEN 1 ELSE 0 END
FROM @tmpDatabases tmpDatabases

UPDATE tmpDatabases
SET tmpDatabases.Selected = SelectedDatabases.Selected
FROM @tmpDatabases tmpDatabases
INNER JOIN @SelectedDatabases SelectedDatabases
ON tmpDatabases.DatabaseName LIKE REPLACE(SelectedDatabases.DatabaseName,’_’,'[_]’)
AND (tmpDatabases.DatabaseType = SelectedDatabases.DatabaseType OR SelectedDatabases.DatabaseType IS NULL)
AND (tmpDatabases.AvailabilityGroup = SelectedDatabases.AvailabilityGroup OR SelectedDatabases.AvailabilityGroup IS NULL)
WHERE SelectedDatabases.Selected = 1

UPDATE tmpDatabases
SET tmpDatabases.Selected = SelectedDatabases.Selected
FROM @tmpDatabases tmpDatabases
INNER JOIN @SelectedDatabases SelectedDatabases
ON tmpDatabases.DatabaseName LIKE REPLACE(SelectedDatabases.DatabaseName,’_’,'[_]’)
AND (tmpDatabases.DatabaseType = SelectedDatabases.DatabaseType OR SelectedDatabases.DatabaseType IS NULL)
AND (tmpDatabases.AvailabilityGroup = SelectedDatabases.AvailabilityGroup OR SelectedDatabases.AvailabilityGroup IS NULL)
WHERE SelectedDatabases.Selected = 0

UPDATE tmpDatabases
SET tmpDatabases.StartPosition = SelectedDatabases2.StartPosition
FROM @tmpDatabases tmpDatabases
INNER JOIN (SELECT tmpDatabases.DatabaseName, MIN(SelectedDatabases.StartPosition) AS StartPosition
FROM @tmpDatabases tmpDatabases
INNER JOIN @SelectedDatabases SelectedDatabases
ON tmpDatabases.DatabaseName LIKE REPLACE(SelectedDatabases.DatabaseName,’_’,'[_]’)
AND (tmpDatabases.DatabaseType = SelectedDatabases.DatabaseType OR SelectedDatabases.DatabaseType IS NULL)
AND (tmpDatabases.AvailabilityGroup = SelectedDatabases.AvailabilityGroup OR SelectedDatabases.AvailabilityGroup IS NULL)
WHERE SelectedDatabases.Selected = 1
GROUP BY tmpDatabases.DatabaseName) SelectedDatabases2
ON tmpDatabases.DatabaseName = SelectedDatabases2.DatabaseName

IF @Databases IS NOT NULL AND (NOT EXISTS(SELECT * FROM @SelectedDatabases) OR EXISTS(SELECT * FROM @SelectedDatabases WHERE DatabaseName IS NULL OR DatabaseName = ”))
BEGIN
INSERT INTO @Errors ([Message], Severity, [State])
SELECT ‘The value for the parameter @Databases is not supported.’, 16, 1
END

—————————————————————————————————-
–// Select availability groups //–
—————————————————————————————————-

IF @AvailabilityGroups IS NOT NULL AND @Version >= 11 AND SERVERPROPERTY(‘IsHadrEnabled’) = 1
BEGIN

SET @AvailabilityGroups = REPLACE(@AvailabilityGroups, CHAR(10), ”)
SET @AvailabilityGroups = REPLACE(@AvailabilityGroups, CHAR(13), ”)

WHILE CHARINDEX(@StringDelimiter + ‘ ‘, @AvailabilityGroups) > 0 SET @AvailabilityGroups = REPLACE(@AvailabilityGroups, @StringDelimiter + ‘ ‘, @StringDelimiter)
WHILE CHARINDEX(‘ ‘ + @StringDelimiter, @AvailabilityGroups) > 0 SET @AvailabilityGroups = REPLACE(@AvailabilityGroups, ‘ ‘ + @StringDelimiter, @StringDelimiter)

SET @AvailabilityGroups = LTRIM(RTRIM(@AvailabilityGroups));

WITH AvailabilityGroups1 (StartPosition, EndPosition, AvailabilityGroupItem) AS
(
SELECT 1 AS StartPosition,
ISNULL(NULLIF(CHARINDEX(@StringDelimiter, @AvailabilityGroups, 1), 0), LEN(@AvailabilityGroups) + 1) AS EndPosition,
SUBSTRING(@AvailabilityGroups, 1, ISNULL(NULLIF(CHARINDEX(@StringDelimiter, @AvailabilityGroups, 1), 0), LEN(@AvailabilityGroups) + 1) – 1) AS AvailabilityGroupItem
WHERE @AvailabilityGroups IS NOT NULL
UNION ALL
SELECT CAST(EndPosition AS int) + 1 AS StartPosition,
ISNULL(NULLIF(CHARINDEX(@StringDelimiter, @AvailabilityGroups, EndPosition + 1), 0), LEN(@AvailabilityGroups) + 1) AS EndPosition,
SUBSTRING(@AvailabilityGroups, EndPosition + 1, ISNULL(NULLIF(CHARINDEX(@StringDelimiter, @AvailabilityGroups, EndPosition + 1), 0), LEN(@AvailabilityGroups) + 1) – EndPosition – 1) AS AvailabilityGroupItem
FROM AvailabilityGroups1
WHERE EndPosition < LEN(@AvailabilityGroups) + 1
),
AvailabilityGroups2 (AvailabilityGroupItem, StartPosition, Selected) AS
(
SELECT CASE WHEN AvailabilityGroupItem LIKE '-%' THEN RIGHT(AvailabilityGroupItem,LEN(AvailabilityGroupItem) – 1) ELSE AvailabilityGroupItem END AS AvailabilityGroupItem,
StartPosition,
CASE WHEN AvailabilityGroupItem LIKE '-%' THEN 0 ELSE 1 END AS Selected
FROM AvailabilityGroups1
),
AvailabilityGroups3 (AvailabilityGroupItem, StartPosition, Selected) AS
(
SELECT CASE WHEN AvailabilityGroupItem = 'ALL_AVAILABILITY_GROUPS' THEN '%' ELSE AvailabilityGroupItem END AS AvailabilityGroupItem,
StartPosition,
Selected
FROM AvailabilityGroups2
),
AvailabilityGroups4 (AvailabilityGroupName, StartPosition, Selected) AS
(
SELECT CASE WHEN LEFT(AvailabilityGroupItem,1) = '[' AND RIGHT(AvailabilityGroupItem,1) = ']' THEN PARSENAME(AvailabilityGroupItem,1) ELSE AvailabilityGroupItem END AS AvailabilityGroupItem,
StartPosition,
Selected
FROM AvailabilityGroups3
)
INSERT INTO @SelectedAvailabilityGroups (AvailabilityGroupName, StartPosition, Selected)
SELECT AvailabilityGroupName, StartPosition, Selected
FROM AvailabilityGroups4
OPTION (MAXRECURSION 0)

UPDATE tmpAvailabilityGroups
SET tmpAvailabilityGroups.Selected = SelectedAvailabilityGroups.Selected
FROM @tmpAvailabilityGroups tmpAvailabilityGroups
INNER JOIN @SelectedAvailabilityGroups SelectedAvailabilityGroups
ON tmpAvailabilityGroups.AvailabilityGroupName LIKE REPLACE(SelectedAvailabilityGroups.AvailabilityGroupName,'_','[_]')
WHERE SelectedAvailabilityGroups.Selected = 1

UPDATE tmpAvailabilityGroups
SET tmpAvailabilityGroups.Selected = SelectedAvailabilityGroups.Selected
FROM @tmpAvailabilityGroups tmpAvailabilityGroups
INNER JOIN @SelectedAvailabilityGroups SelectedAvailabilityGroups
ON tmpAvailabilityGroups.AvailabilityGroupName LIKE REPLACE(SelectedAvailabilityGroups.AvailabilityGroupName,'_','[_]')
WHERE SelectedAvailabilityGroups.Selected = 0

UPDATE tmpAvailabilityGroups
SET tmpAvailabilityGroups.StartPosition = SelectedAvailabilityGroups2.StartPosition
FROM @tmpAvailabilityGroups tmpAvailabilityGroups
INNER JOIN (SELECT tmpAvailabilityGroups.AvailabilityGroupName, MIN(SelectedAvailabilityGroups.StartPosition) AS StartPosition
FROM @tmpAvailabilityGroups tmpAvailabilityGroups
INNER JOIN @SelectedAvailabilityGroups SelectedAvailabilityGroups
ON tmpAvailabilityGroups.AvailabilityGroupName LIKE REPLACE(SelectedAvailabilityGroups.AvailabilityGroupName,'_','[_]')
WHERE SelectedAvailabilityGroups.Selected = 1
GROUP BY tmpAvailabilityGroups.AvailabilityGroupName) SelectedAvailabilityGroups2
ON tmpAvailabilityGroups.AvailabilityGroupName = SelectedAvailabilityGroups2.AvailabilityGroupName

UPDATE tmpDatabases
SET tmpDatabases.StartPosition = tmpAvailabilityGroups.StartPosition,
tmpDatabases.Selected = 1
FROM @tmpDatabases tmpDatabases
INNER JOIN @tmpDatabasesAvailabilityGroups tmpDatabasesAvailabilityGroups ON tmpDatabases.DatabaseName = tmpDatabasesAvailabilityGroups.DatabaseName
INNER JOIN @tmpAvailabilityGroups tmpAvailabilityGroups ON tmpDatabasesAvailabilityGroups.AvailabilityGroupName = tmpAvailabilityGroups.AvailabilityGroupName
WHERE tmpAvailabilityGroups.Selected = 1

END

IF @AvailabilityGroups IS NOT NULL AND (NOT EXISTS(SELECT * FROM @SelectedAvailabilityGroups) OR EXISTS(SELECT * FROM @SelectedAvailabilityGroups WHERE AvailabilityGroupName IS NULL OR AvailabilityGroupName = '') OR @Version 0 SET @Indexes = REPLACE(@Indexes, @StringDelimiter + ‘ ‘, @StringDelimiter)
WHILE CHARINDEX(‘ ‘ + @StringDelimiter, @Indexes) > 0 SET @Indexes = REPLACE(@Indexes, ‘ ‘ + @StringDelimiter, @StringDelimiter)

SET @Indexes = LTRIM(RTRIM(@Indexes));

WITH Indexes1 (StartPosition, EndPosition, IndexItem) AS
(
SELECT 1 AS StartPosition,
ISNULL(NULLIF(CHARINDEX(@StringDelimiter, @Indexes, 1), 0), LEN(@Indexes) + 1) AS EndPosition,
SUBSTRING(@Indexes, 1, ISNULL(NULLIF(CHARINDEX(@StringDelimiter, @Indexes, 1), 0), LEN(@Indexes) + 1) – 1) AS IndexItem
WHERE @Indexes IS NOT NULL
UNION ALL
SELECT CAST(EndPosition AS int) + 1 AS StartPosition,
ISNULL(NULLIF(CHARINDEX(@StringDelimiter, @Indexes, EndPosition + 1), 0), LEN(@Indexes) + 1) AS EndPosition,
SUBSTRING(@Indexes, EndPosition + 1, ISNULL(NULLIF(CHARINDEX(@StringDelimiter, @Indexes, EndPosition + 1), 0), LEN(@Indexes) + 1) – EndPosition – 1) AS IndexItem
FROM Indexes1
WHERE EndPosition < LEN(@Indexes) + 1
),
Indexes2 (IndexItem, StartPosition, Selected) AS
(
SELECT CASE WHEN IndexItem LIKE '-%' THEN RIGHT(IndexItem,LEN(IndexItem) – 1) ELSE IndexItem END AS IndexItem,
StartPosition,
CASE WHEN IndexItem LIKE '-%' THEN 0 ELSE 1 END AS Selected
FROM Indexes1
),
Indexes3 (IndexItem, StartPosition, Selected) AS
(
SELECT CASE WHEN IndexItem = 'ALL_INDEXES' THEN '%.%.%.%' ELSE IndexItem END AS IndexItem,
StartPosition,
Selected
FROM Indexes2
),
Indexes4 (DatabaseName, SchemaName, ObjectName, IndexName, StartPosition, Selected) AS
(
SELECT CASE WHEN PARSENAME(IndexItem,4) IS NULL THEN PARSENAME(IndexItem,3) ELSE PARSENAME(IndexItem,4) END AS DatabaseName,
CASE WHEN PARSENAME(IndexItem,4) IS NULL THEN PARSENAME(IndexItem,2) ELSE PARSENAME(IndexItem,3) END AS SchemaName,
CASE WHEN PARSENAME(IndexItem,4) IS NULL THEN PARSENAME(IndexItem,1) ELSE PARSENAME(IndexItem,2) END AS ObjectName,
CASE WHEN PARSENAME(IndexItem,4) IS NULL THEN '%' ELSE PARSENAME(IndexItem,1) END AS IndexName,
StartPosition,
Selected
FROM Indexes3
)
INSERT INTO @SelectedIndexes (DatabaseName, SchemaName, ObjectName, IndexName, StartPosition, Selected)
SELECT DatabaseName, SchemaName, ObjectName, IndexName, StartPosition, Selected
FROM Indexes4
OPTION (MAXRECURSION 0)

—————————————————————————————————-
–// Select actions //–
—————————————————————————————————-

SET @FragmentationLow = REPLACE(@FragmentationLow, @StringDelimiter + ' ', @StringDelimiter);

WITH FragmentationLow (StartPosition, EndPosition, [Action]) AS
(
SELECT 1 AS StartPosition,
ISNULL(NULLIF(CHARINDEX(@StringDelimiter, @FragmentationLow, 1), 0), LEN(@FragmentationLow) + 1) AS EndPosition,
SUBSTRING(@FragmentationLow, 1, ISNULL(NULLIF(CHARINDEX(@StringDelimiter, @FragmentationLow, 1), 0), LEN(@FragmentationLow) + 1) – 1) AS [Action]
WHERE @FragmentationLow IS NOT NULL
UNION ALL
SELECT CAST(EndPosition AS int) + 1 AS StartPosition,
ISNULL(NULLIF(CHARINDEX(@StringDelimiter, @FragmentationLow, EndPosition + 1), 0), LEN(@FragmentationLow) + 1) AS EndPosition,
SUBSTRING(@FragmentationLow, EndPosition + 1, ISNULL(NULLIF(CHARINDEX(@StringDelimiter, @FragmentationLow, EndPosition + 1), 0), LEN(@FragmentationLow) + 1) – EndPosition – 1) AS [Action]
FROM FragmentationLow
WHERE EndPosition < LEN(@FragmentationLow) + 1
)
INSERT INTO @ActionsPreferred(FragmentationGroup, [Priority], [Action])
SELECT 'Low' AS FragmentationGroup,
ROW_NUMBER() OVER(ORDER BY StartPosition ASC) AS [Priority],
[Action]
FROM FragmentationLow
OPTION (MAXRECURSION 0)

SET @FragmentationMedium = REPLACE(@FragmentationMedium, @StringDelimiter + ' ', @StringDelimiter);

WITH FragmentationMedium (StartPosition, EndPosition, [Action]) AS
(
SELECT 1 AS StartPosition,
ISNULL(NULLIF(CHARINDEX(@StringDelimiter, @FragmentationMedium, 1), 0), LEN(@FragmentationMedium) + 1) AS EndPosition,
SUBSTRING(@FragmentationMedium, 1, ISNULL(NULLIF(CHARINDEX(@StringDelimiter, @FragmentationMedium, 1), 0), LEN(@FragmentationMedium) + 1) – 1) AS [Action]
WHERE @FragmentationMedium IS NOT NULL
UNION ALL
SELECT CAST(EndPosition AS int) + 1 AS StartPosition,
ISNULL(NULLIF(CHARINDEX(@StringDelimiter, @FragmentationMedium, EndPosition + 1), 0), LEN(@FragmentationMedium) + 1) AS EndPosition,
SUBSTRING(@FragmentationMedium, EndPosition + 1, ISNULL(NULLIF(CHARINDEX(@StringDelimiter, @FragmentationMedium, EndPosition + 1), 0), LEN(@FragmentationMedium) + 1) – EndPosition – 1) AS [Action]
FROM FragmentationMedium
WHERE EndPosition < LEN(@FragmentationMedium) + 1
)
INSERT INTO @ActionsPreferred(FragmentationGroup, [Priority], [Action])
SELECT 'Medium' AS FragmentationGroup,
ROW_NUMBER() OVER(ORDER BY StartPosition ASC) AS [Priority],
[Action]
FROM FragmentationMedium
OPTION (MAXRECURSION 0)

SET @FragmentationHigh = REPLACE(@FragmentationHigh, @StringDelimiter + ' ', @StringDelimiter);

WITH FragmentationHigh (StartPosition, EndPosition, [Action]) AS
(
SELECT 1 AS StartPosition,
ISNULL(NULLIF(CHARINDEX(@StringDelimiter, @FragmentationHigh, 1), 0), LEN(@FragmentationHigh) + 1) AS EndPosition,
SUBSTRING(@FragmentationHigh, 1, ISNULL(NULLIF(CHARINDEX(@StringDelimiter, @FragmentationHigh, 1), 0), LEN(@FragmentationHigh) + 1) – 1) AS [Action]
WHERE @FragmentationHigh IS NOT NULL
UNION ALL
SELECT CAST(EndPosition AS int) + 1 AS StartPosition,
ISNULL(NULLIF(CHARINDEX(@StringDelimiter, @FragmentationHigh, EndPosition + 1), 0), LEN(@FragmentationHigh) + 1) AS EndPosition,
SUBSTRING(@FragmentationHigh, EndPosition + 1, ISNULL(NULLIF(CHARINDEX(@StringDelimiter, @FragmentationHigh, EndPosition + 1), 0), LEN(@FragmentationHigh) + 1) – EndPosition – 1) AS [Action]
FROM FragmentationHigh
WHERE EndPosition 1)
BEGIN
INSERT INTO @Errors ([Message], Severity, [State])
SELECT ‘The value for the parameter @FragmentationLow is not supported.’, 16, 2
END

—————————————————————————————————-

IF EXISTS (SELECT [Action] FROM @ActionsPreferred WHERE FragmentationGroup = ‘Medium’ AND [Action] NOT IN(SELECT * FROM @Actions))
BEGIN
INSERT INTO @Errors ([Message], Severity, [State])
SELECT ‘The value for the parameter @FragmentationMedium is not supported.’, 16, 1
END

IF EXISTS (SELECT * FROM @ActionsPreferred WHERE FragmentationGroup = ‘Medium’ GROUP BY [Action] HAVING COUNT(*) > 1)
BEGIN
INSERT INTO @Errors ([Message], Severity, [State])
SELECT ‘The value for the parameter @FragmentationMedium is not supported.’, 16, 2
END

—————————————————————————————————-

IF EXISTS (SELECT [Action] FROM @ActionsPreferred WHERE FragmentationGroup = ‘High’ AND [Action] NOT IN(SELECT * FROM @Actions))
BEGIN
INSERT INTO @Errors ([Message], Severity, [State])
SELECT ‘The value for the parameter @FragmentationHigh is not supported.’, 16, 1
END

IF EXISTS (SELECT * FROM @ActionsPreferred WHERE FragmentationGroup = ‘High’ GROUP BY [Action] HAVING COUNT(*) > 1)
BEGIN
INSERT INTO @Errors ([Message], Severity, [State])
SELECT ‘The value for the parameter @FragmentationHigh is not supported.’, 16, 2
END

—————————————————————————————————-

IF @FragmentationLevel1 = 100 OR @FragmentationLevel1 IS NULL
BEGIN
INSERT INTO @Errors ([Message], Severity, [State])
SELECT ‘The value for the parameter @FragmentationLevel1 is not supported.’, 16, 1
END

IF @FragmentationLevel1 >= @FragmentationLevel2
BEGIN
INSERT INTO @Errors ([Message], Severity, [State])
SELECT ‘The value for the parameter @FragmentationLevel1 is not supported.’, 16, 2
END

—————————————————————————————————-

IF @FragmentationLevel2 = 100 OR @FragmentationLevel2 IS NULL
BEGIN
INSERT INTO @Errors ([Message], Severity, [State])
SELECT ‘The value for the parameter @FragmentationLevel2 is not supported.’, 16, 1
END

IF @FragmentationLevel2 <= @FragmentationLevel1
BEGIN
INSERT INTO @Errors ([Message], Severity, [State])
SELECT 'The value for the parameter @FragmentationLevel2 is not supported.', 16, 2
END

—————————————————————————————————-

IF @MinNumberOfPages < 0 OR @MinNumberOfPages IS NULL
BEGIN
INSERT INTO @Errors ([Message], Severity, [State])
SELECT 'The value for the parameter @MinNumberOfPages is not supported.', 16, 1
END

—————————————————————————————————-

IF @MaxNumberOfPages < 0
BEGIN
INSERT INTO @Errors ([Message], Severity, [State])
SELECT 'The value for the parameter @MaxNumberOfPages is not supported.', 16, 1
END

—————————————————————————————————-

IF @SortInTempdb NOT IN('Y','N') OR @SortInTempdb IS NULL
BEGIN
INSERT INTO @Errors ([Message], Severity, [State])
SELECT 'The value for the parameter @SortInTempdb is not supported.', 16, 1
END

—————————————————————————————————-

IF @MaxDOP 64
BEGIN
INSERT INTO @Errors ([Message], Severity, [State])
SELECT ‘The value for the parameter @MaxDOP is not supported.’, 16, 1
END

—————————————————————————————————-

IF @FillFactor 100
BEGIN
INSERT INTO @Errors ([Message], Severity, [State])
SELECT ‘The value for the parameter @FillFactor is not supported.’, 16, 1
END

—————————————————————————————————-

IF @PadIndex NOT IN(‘Y’,’N’)
BEGIN
INSERT INTO @Errors ([Message], Severity, [State])
SELECT ‘The value for the parameter @PadIndex is not supported.’, 16, 1
END

—————————————————————————————————-

IF @LOBCompaction NOT IN(‘Y’,’N’) OR @LOBCompaction IS NULL
BEGIN
INSERT INTO @Errors ([Message], Severity, [State])
SELECT ‘The value for the parameter @LOBCompaction is not supported.’, 16, 1
END

—————————————————————————————————-

IF @UpdateStatistics NOT IN(‘ALL’,’COLUMNS’,’INDEX’)
BEGIN
INSERT INTO @Errors ([Message], Severity, [State])
SELECT ‘The value for the parameter @UpdateStatistics is not supported.’, 16, 1
END

—————————————————————————————————-

IF @OnlyModifiedStatistics NOT IN(‘Y’,’N’) OR @OnlyModifiedStatistics IS NULL
BEGIN
INSERT INTO @Errors ([Message], Severity, [State])
SELECT ‘The value for the parameter @OnlyModifiedStatistics is not supported.’, 16, 1
END

—————————————————————————————————-

IF @StatisticsModificationLevel 100
BEGIN
INSERT INTO @Errors ([Message], Severity, [State])
SELECT ‘The value for the parameter @StatisticsModificationLevel is not supported.’, 16, 1
END

—————————————————————————————————-

IF @OnlyModifiedStatistics = ‘Y’ AND @StatisticsModificationLevel IS NOT NULL
BEGIN
INSERT INTO @Errors ([Message], Severity, [State])
SELECT ‘You can only specify one of the parameters @OnlyModifiedStatistics and @StatisticsModificationLevel.’, 16, 1
END

—————————————————————————————————-

IF @StatisticsSample 100
BEGIN
INSERT INTO @Errors ([Message], Severity, [State])
SELECT ‘The value for the parameter @StatisticsSample is not supported.’, 16, 1
END

—————————————————————————————————-

IF @StatisticsResample NOT IN(‘Y’,’N’) OR @StatisticsResample IS NULL
BEGIN
INSERT INTO @Errors ([Message], Severity, [State])
SELECT ‘The value for the parameter @StatisticsResample is not supported.’, 16, 1
END

IF @StatisticsResample = ‘Y’ AND @StatisticsSample IS NOT NULL
BEGIN
INSERT INTO @Errors ([Message], Severity, [State])
SELECT ‘The value for the parameter @StatisticsResample is not supported.’, 16, 2
END

—————————————————————————————————-

IF @PartitionLevel NOT IN(‘Y’,’N’) OR @PartitionLevel IS NULL
BEGIN
INSERT INTO @Errors ([Message], Severity, [State])
SELECT ‘The value for the parameter @PartitionLevel is not supported.’, 16, 1
END

—————————————————————————————————-

IF @MSShippedObjects NOT IN(‘Y’,’N’) OR @MSShippedObjects IS NULL
BEGIN
INSERT INTO @Errors ([Message], Severity, [State])
SELECT ‘The value for the parameter @MSShippedObjects is not supported.’, 16, 1
END

—————————————————————————————————-

IF EXISTS(SELECT * FROM @SelectedIndexes WHERE DatabaseName IS NULL OR SchemaName IS NULL OR ObjectName IS NULL OR IndexName IS NULL)
BEGIN
INSERT INTO @Errors ([Message], Severity, [State])
SELECT ‘The value for the parameter @Indexes is not supported.’, 16, 1
END

IF @Indexes IS NOT NULL AND NOT EXISTS(SELECT * FROM @SelectedIndexes)
BEGIN
INSERT INTO @Errors ([Message], Severity, [State])
SELECT ‘The value for the parameter @Indexes is not supported.’, 16, 2
END

—————————————————————————————————-

IF @TimeLimit < 0
BEGIN
INSERT INTO @Errors ([Message], Severity, [State])
SELECT 'The value for the parameter @TimeLimit is not supported.', 16, 1
END

—————————————————————————————————-

IF @Delay < 0
BEGIN
INSERT INTO @Errors ([Message], Severity, [State])
SELECT 'The value for the parameter @Delay is not supported.', 16, 1
END

—————————————————————————————————-

IF @WaitAtLowPriorityMaxDuration < 0
BEGIN
INSERT INTO @Errors ([Message], Severity, [State])
SELECT 'The value for the parameter @WaitAtLowPriorityMaxDuration is not supported.', 16, 1
END

IF @WaitAtLowPriorityMaxDuration IS NOT NULL AND @Version < 12
BEGIN
INSERT INTO @Errors ([Message], Severity, [State])
SELECT 'The value for the parameter @WaitAtLowPriorityMaxDuration is not supported.', 16, 2
END

—————————————————————————————————-

IF @WaitAtLowPriorityAbortAfterWait NOT IN('NONE','SELF','BLOCKERS')
BEGIN
INSERT INTO @Errors ([Message], Severity, [State])
SELECT 'The value for the parameter @WaitAtLowPriorityAbortAfterWait is not supported.', 16, 1
END

IF @WaitAtLowPriorityAbortAfterWait IS NOT NULL AND @Version = 14 OR SERVERPROPERTY(‘EngineEdition’) IN (5, 8))
BEGIN
INSERT INTO @Errors ([Message], Severity, [State])
SELECT ‘The value for the parameter @Resumable is not supported.’, 16, 2
END

IF @Resumable = ‘Y’ AND @SortInTempdb = ‘Y’
BEGIN
INSERT INTO @Errors ([Message], Severity, [State])
SELECT ‘You can only specify one of the parameters @Resumable and @SortInTempdb.’, 16, 3
END

—————————————————————————————————-

IF @LockTimeout 1
BEGIN
INSERT INTO @Errors ([Message], Severity, [State])
SELECT ‘The value for the parameter @StringDelimiter is not supported.’, 16, 1
END

—————————————————————————————————-

IF @DatabaseOrder NOT IN(‘DATABASE_NAME_ASC’,’DATABASE_NAME_DESC’,’DATABASE_SIZE_ASC’,’DATABASE_SIZE_DESC’)
BEGIN
INSERT INTO @Errors ([Message], Severity, [State])
SELECT ‘The value for the parameter @DatabaseOrder is not supported.’, 16, 1
END

IF @DatabaseOrder IS NOT NULL AND SERVERPROPERTY(‘EngineEdition’) = 5
BEGIN
INSERT INTO @Errors ([Message], Severity, [State])
SELECT ‘The value for the parameter @DatabaseOrder is not supported.’, 16, 2
END

—————————————————————————————————-

IF @DatabasesInParallel NOT IN(‘Y’,’N’) OR @DatabasesInParallel IS NULL
BEGIN
INSERT INTO @Errors ([Message], Severity, [State])
SELECT ‘The value for the parameter @DatabasesInParallel is not supported.’, 16, 1
END

IF @DatabasesInParallel = ‘Y’ AND SERVERPROPERTY(‘EngineEdition’) = 5
BEGIN
INSERT INTO @Errors ([Message], Severity, [State])
SELECT ‘The value for the parameter @DatabasesInParallel is not supported.’, 16, 2
END

—————————————————————————————————-

IF LEN(@ExecuteAsUser) > 128
BEGIN
INSERT INTO @Errors ([Message], Severity, [State])
SELECT ‘The value for the parameter @ExecuteAsUser is not supported.’, 16, 1
END

—————————————————————————————————-

IF @LogToTable NOT IN(‘Y’,’N’) OR @LogToTable IS NULL
BEGIN
INSERT INTO @Errors ([Message], Severity, [State])
SELECT ‘The value for the parameter @LogToTable is not supported.’, 16, 1
END

—————————————————————————————————-

IF @Execute NOT IN(‘Y’,’N’) OR @Execute IS NULL
BEGIN
INSERT INTO @Errors ([Message], Severity, [State])
SELECT ‘The value for the parameter @Execute is not supported.’, 16, 1
END

—————————————————————————————————-

IF EXISTS(SELECT * FROM @Errors)
BEGIN
INSERT INTO @Errors ([Message], Severity, [State])
SELECT ‘The documentation is available at https://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html.’, 16, 1
END

—————————————————————————————————-
–// Check that selected databases and availability groups exist //–
—————————————————————————————————-

SET @ErrorMessage = ”
SELECT @ErrorMessage = @ErrorMessage + QUOTENAME(DatabaseName) + ‘, ‘
FROM @SelectedDatabases
WHERE DatabaseName NOT LIKE ‘%[%]%’
AND DatabaseName NOT IN (SELECT DatabaseName FROM @tmpDatabases)
IF @@ROWCOUNT > 0
BEGIN
INSERT INTO @Errors ([Message], Severity, [State])
SELECT ‘The following databases in the @Databases parameter do not exist: ‘ + LEFT(@ErrorMessage,LEN(@ErrorMessage)-1) + ‘.’, 10, 1
END

SET @ErrorMessage = ”
SELECT @ErrorMessage = @ErrorMessage + QUOTENAME(DatabaseName) + ‘, ‘
FROM @SelectedIndexes
WHERE DatabaseName NOT LIKE ‘%[%]%’
AND DatabaseName NOT IN (SELECT DatabaseName FROM @tmpDatabases)
IF @@ROWCOUNT > 0
BEGIN
INSERT INTO @Errors ([Message], Severity, [State])
SELECT ‘The following databases in the @Indexes parameter do not exist: ‘ + LEFT(@ErrorMessage,LEN(@ErrorMessage)-1) + ‘.’, 10, 1
END

SET @ErrorMessage = ”
SELECT @ErrorMessage = @ErrorMessage + QUOTENAME(AvailabilityGroupName) + ‘, ‘
FROM @SelectedAvailabilityGroups
WHERE AvailabilityGroupName NOT LIKE ‘%[%]%’
AND AvailabilityGroupName NOT IN (SELECT AvailabilityGroupName FROM @tmpAvailabilityGroups)
IF @@ROWCOUNT > 0
BEGIN
INSERT INTO @Errors ([Message], Severity, [State])
SELECT ‘The following availability groups do not exist: ‘ + LEFT(@ErrorMessage,LEN(@ErrorMessage)-1) + ‘.’, 10, 1
END

SET @ErrorMessage = ”
SELECT @ErrorMessage = @ErrorMessage + QUOTENAME(DatabaseName) + ‘, ‘
FROM @SelectedIndexes
WHERE DatabaseName NOT LIKE ‘%[%]%’
AND DatabaseName IN (SELECT DatabaseName FROM @tmpDatabases)
AND DatabaseName NOT IN (SELECT DatabaseName FROM @tmpDatabases WHERE Selected = 1)
IF @@ROWCOUNT > 0
BEGIN
INSERT INTO @Errors ([Message], Severity, [State])
SELECT ‘The following databases have been selected in the @Indexes parameter, but not in the @Databases or @AvailabilityGroups parameters: ‘ + LEFT(@ErrorMessage,LEN(@ErrorMessage)-1) + ‘.’, 10, 1
END

—————————————————————————————————-
–// Raise errors //–
—————————————————————————————————-

DECLARE ErrorCursor CURSOR FAST_FORWARD FOR SELECT [Message], Severity, [State] FROM @Errors ORDER BY [ID] ASC

OPEN ErrorCursor

FETCH ErrorCursor INTO @CurrentMessage, @CurrentSeverity, @CurrentState

WHILE @@FETCH_STATUS = 0
BEGIN
RAISERROR(‘%s’, @CurrentSeverity, @CurrentState, @CurrentMessage) WITH NOWAIT
RAISERROR(@EmptyLine, 10, 1) WITH NOWAIT

FETCH NEXT FROM ErrorCursor INTO @CurrentMessage, @CurrentSeverity, @CurrentState
END

CLOSE ErrorCursor

DEALLOCATE ErrorCursor

IF EXISTS (SELECT * FROM @Errors WHERE Severity >= 16)
BEGIN
SET @ReturnCode = 50000
GOTO Logging
END

—————————————————————————————————-
–// Should statistics be updated on the partition level? //–
—————————————————————————————————-

SET @PartitionLevelStatistics = CASE WHEN @PartitionLevel = ‘Y’ AND ((@Version >= 12.05 AND @Version = 13.04422 OR SERVERPROPERTY(‘EngineEdition’) IN (5,8)) THEN 1 ELSE 0 END

—————————————————————————————————-
–// Update database order //–
—————————————————————————————————-

IF @DatabaseOrder IN(‘DATABASE_SIZE_ASC’,’DATABASE_SIZE_DESC’)
BEGIN
UPDATE tmpDatabases
SET DatabaseSize = (SELECT SUM(CAST(size AS bigint)) FROM sys.master_files WHERE [type] = 0 AND database_id = DB_ID(tmpDatabases.DatabaseName))
FROM @tmpDatabases tmpDatabases
END

IF @DatabaseOrder IS NULL
BEGIN
WITH tmpDatabases AS (
SELECT DatabaseName, [Order], ROW_NUMBER() OVER (ORDER BY StartPosition ASC, DatabaseName ASC) AS RowNumber
FROM @tmpDatabases tmpDatabases
WHERE Selected = 1
)
UPDATE tmpDatabases
SET [Order] = RowNumber
END
ELSE
IF @DatabaseOrder = ‘DATABASE_NAME_ASC’
BEGIN
WITH tmpDatabases AS (
SELECT DatabaseName, [Order], ROW_NUMBER() OVER (ORDER BY DatabaseName ASC) AS RowNumber
FROM @tmpDatabases tmpDatabases
WHERE Selected = 1
)
UPDATE tmpDatabases
SET [Order] = RowNumber
END
ELSE
IF @DatabaseOrder = ‘DATABASE_NAME_DESC’
BEGIN
WITH tmpDatabases AS (
SELECT DatabaseName, [Order], ROW_NUMBER() OVER (ORDER BY DatabaseName DESC) AS RowNumber
FROM @tmpDatabases tmpDatabases
WHERE Selected = 1
)
UPDATE tmpDatabases
SET [Order] = RowNumber
END
ELSE
IF @DatabaseOrder = ‘DATABASE_SIZE_ASC’
BEGIN
WITH tmpDatabases AS (
SELECT DatabaseName, [Order], ROW_NUMBER() OVER (ORDER BY DatabaseSize ASC) AS RowNumber
FROM @tmpDatabases tmpDatabases
WHERE Selected = 1
)
UPDATE tmpDatabases
SET [Order] = RowNumber
END
ELSE
IF @DatabaseOrder = ‘DATABASE_SIZE_DESC’
BEGIN
WITH tmpDatabases AS (
SELECT DatabaseName, [Order], ROW_NUMBER() OVER (ORDER BY DatabaseSize DESC) AS RowNumber
FROM @tmpDatabases tmpDatabases
WHERE Selected = 1
)
UPDATE tmpDatabases
SET [Order] = RowNumber
END

—————————————————————————————————-
–// Update the queue //–
—————————————————————————————————-

IF @DatabasesInParallel = ‘Y’
BEGIN

BEGIN TRY

SELECT @QueueID = QueueID
FROM dbo.[Queue]
WHERE SchemaName = @SchemaName
AND ObjectName = @ObjectName
AND [Parameters] = @Parameters

IF @QueueID IS NULL
BEGIN
BEGIN TRANSACTION

SELECT @QueueID = QueueID
FROM dbo.[Queue] WITH (UPDLOCK, HOLDLOCK)
WHERE SchemaName = @SchemaName
AND ObjectName = @ObjectName
AND [Parameters] = @Parameters

IF @QueueID IS NULL
BEGIN
INSERT INTO dbo.[Queue] (SchemaName, ObjectName, [Parameters])
SELECT @SchemaName, @ObjectName, @Parameters

SET @QueueID = SCOPE_IDENTITY()
END

COMMIT TRANSACTION
END

BEGIN TRANSACTION

UPDATE [Queue]
SET QueueStartTime = SYSDATETIME(),
SessionID = @@SPID,
RequestID = (SELECT request_id FROM sys.dm_exec_requests WHERE session_id = @@SPID),
RequestStartTime = (SELECT start_time FROM sys.dm_exec_requests WHERE session_id = @@SPID)
FROM dbo.[Queue] [Queue]
WHERE QueueID = @QueueID
AND NOT EXISTS (SELECT *
FROM sys.dm_exec_requests
WHERE session_id = [Queue].SessionID
AND request_id = [Queue].RequestID
AND start_time = [Queue].RequestStartTime)
AND NOT EXISTS (SELECT *
FROM dbo.QueueDatabase QueueDatabase
INNER JOIN sys.dm_exec_requests ON QueueDatabase.SessionID = session_id AND QueueDatabase.RequestID = request_id AND QueueDatabase.RequestStartTime = start_time
WHERE QueueDatabase.QueueID = @QueueID)

IF @@ROWCOUNT = 1
BEGIN
INSERT INTO dbo.QueueDatabase (QueueID, DatabaseName)
SELECT @QueueID AS QueueID,
DatabaseName
FROM @tmpDatabases tmpDatabases
WHERE Selected = 1
AND NOT EXISTS (SELECT * FROM dbo.QueueDatabase WHERE DatabaseName = tmpDatabases.DatabaseName AND QueueID = @QueueID)

DELETE QueueDatabase
FROM dbo.QueueDatabase QueueDatabase
WHERE QueueID = @QueueID
AND NOT EXISTS (SELECT * FROM @tmpDatabases tmpDatabases WHERE DatabaseName = QueueDatabase.DatabaseName AND Selected = 1)

UPDATE QueueDatabase
SET DatabaseOrder = tmpDatabases.[Order]
FROM dbo.QueueDatabase QueueDatabase
INNER JOIN @tmpDatabases tmpDatabases ON QueueDatabase.DatabaseName = tmpDatabases.DatabaseName
WHERE QueueID = @QueueID
END

COMMIT TRANSACTION

SELECT @QueueStartTime = QueueStartTime
FROM dbo.[Queue]
WHERE QueueID = @QueueID

END TRY

BEGIN CATCH
IF XACT_STATE() 0
BEGIN
ROLLBACK TRANSACTION
END
SET @ErrorMessage = ‘Msg ‘ + CAST(ERROR_NUMBER() AS nvarchar) + ‘, ‘ + ISNULL(ERROR_MESSAGE(),”)
RAISERROR(‘%s’,16,1,@ErrorMessage) WITH NOWAIT
RAISERROR(@EmptyLine,10,1) WITH NOWAIT
SET @ReturnCode = ERROR_NUMBER()
GOTO Logging
END CATCH

END

—————————————————————————————————-
–// Execute commands //–
—————————————————————————————————-

WHILE (1 = 1)
BEGIN

IF @DatabasesInParallel = ‘Y’
BEGIN
UPDATE QueueDatabase
SET DatabaseStartTime = NULL,
SessionID = NULL,
RequestID = NULL,
RequestStartTime = NULL
FROM dbo.QueueDatabase QueueDatabase
WHERE QueueID = @QueueID
AND DatabaseStartTime IS NOT NULL
AND DatabaseEndTime IS NULL
AND NOT EXISTS (SELECT * FROM sys.dm_exec_requests WHERE session_id = QueueDatabase.SessionID AND request_id = QueueDatabase.RequestID AND start_time = QueueDatabase.RequestStartTime)

UPDATE QueueDatabase
SET DatabaseStartTime = SYSDATETIME(),
DatabaseEndTime = NULL,
SessionID = @@SPID,
RequestID = (SELECT request_id FROM sys.dm_exec_requests WHERE session_id = @@SPID),
RequestStartTime = (SELECT start_time FROM sys.dm_exec_requests WHERE session_id = @@SPID),
@CurrentDatabaseName = DatabaseName
FROM (SELECT TOP 1 DatabaseStartTime,
DatabaseEndTime,
SessionID,
RequestID,
RequestStartTime,
DatabaseName
FROM dbo.QueueDatabase
WHERE QueueID = @QueueID
AND (DatabaseStartTime < @QueueStartTime OR DatabaseStartTime IS NULL)
AND NOT (DatabaseStartTime IS NOT NULL AND DatabaseEndTime IS NULL)
ORDER BY DatabaseOrder ASC
) QueueDatabase
END
ELSE
BEGIN
SELECT TOP 1 @CurrentDBID = ID,
@CurrentDatabaseName = DatabaseName
FROM @tmpDatabases
WHERE Selected = 1
AND Completed = 0
ORDER BY [Order] ASC
END

IF @@ROWCOUNT = 0
BEGIN
BREAK
END

SET @CurrentDatabase_sp_executesql = QUOTENAME(@CurrentDatabaseName) + '.sys.sp_executesql'

IF @ExecuteAsUser IS NOT NULL
BEGIN
SET @CurrentCommand = ''
SET @CurrentCommand += 'IF EXISTS(SELECT * FROM sys.database_principals database_principals WHERE database_principals.[name] = @ParamExecuteAsUser) BEGIN SET @ParamExecuteAsUserExists = 1 END ELSE BEGIN SET @ParamExecuteAsUserExists = 0 END'

EXECUTE @CurrentDatabase_sp_executesql @stmt = @CurrentCommand, @params = N'@ParamExecuteAsUser sysname, @ParamExecuteAsUserExists bit OUTPUT', @ParamExecuteAsUser = @ExecuteAsUser, @ParamExecuteAsUserExists = @CurrentExecuteAsUserExists OUTPUT
END

BEGIN
SET @DatabaseMessage = 'Date and time: ' + CONVERT(nvarchar,SYSDATETIME(),120)
RAISERROR('%s',10,1,@DatabaseMessage) WITH NOWAIT

SET @DatabaseMessage = 'Database: ' + QUOTENAME(@CurrentDatabaseName)
RAISERROR('%s',10,1,@DatabaseMessage) WITH NOWAIT
END

SELECT @CurrentUserAccess = user_access_desc,
@CurrentIsReadOnly = is_read_only,
@CurrentDatabaseState = state_desc,
@CurrentInStandby = is_in_standby,
@CurrentRecoveryModel = recovery_model_desc
FROM sys.databases
WHERE [name] = @CurrentDatabaseName

BEGIN
SET @DatabaseMessage = 'State: ' + @CurrentDatabaseState
RAISERROR('%s',10,1,@DatabaseMessage) WITH NOWAIT

SET @DatabaseMessage = 'Standby: ' + CASE WHEN @CurrentInStandby = 1 THEN 'Yes' ELSE 'No' END
RAISERROR('%s',10,1,@DatabaseMessage) WITH NOWAIT

SET @DatabaseMessage = 'Updateability: ' + CASE WHEN @CurrentIsReadOnly = 1 THEN 'READ_ONLY' WHEN @CurrentIsReadOnly = 0 THEN 'READ_WRITE' END
RAISERROR('%s',10,1,@DatabaseMessage) WITH NOWAIT

SET @DatabaseMessage = 'User access: ' + @CurrentUserAccess
RAISERROR('%s',10,1,@DatabaseMessage) WITH NOWAIT

SET @DatabaseMessage = 'Recovery model: ' + @CurrentRecoveryModel
RAISERROR('%s',10,1,@DatabaseMessage) WITH NOWAIT
END

IF @CurrentDatabaseState = 'ONLINE' AND SERVERPROPERTY('EngineEdition') 5
BEGIN
IF EXISTS (SELECT * FROM sys.database_recovery_status WHERE database_id = DB_ID(@CurrentDatabaseName) AND database_guid IS NOT NULL)
BEGIN
SET @CurrentIsDatabaseAccessible = 1
END
ELSE
BEGIN
SET @CurrentIsDatabaseAccessible = 0
END
END

IF @Version >= 11 AND SERVERPROPERTY(‘IsHadrEnabled’) = 1
BEGIN
SELECT @CurrentReplicaID = databases.replica_id
FROM sys.databases databases
INNER JOIN sys.availability_replicas availability_replicas ON databases.replica_id = availability_replicas.replica_id
WHERE databases.[name] = @CurrentDatabaseName

SELECT @CurrentAvailabilityGroupID = group_id
FROM sys.availability_replicas
WHERE replica_id = @CurrentReplicaID

SELECT @CurrentAvailabilityGroupRole = role_desc
FROM sys.dm_hadr_availability_replica_states
WHERE replica_id = @CurrentReplicaID

SELECT @CurrentAvailabilityGroup = [name]
FROM sys.availability_groups
WHERE group_id = @CurrentAvailabilityGroupID
END

IF SERVERPROPERTY(‘EngineEdition’) 5
BEGIN
SELECT @CurrentDatabaseMirroringRole = UPPER(mirroring_role_desc)
FROM sys.database_mirroring
WHERE database_id = DB_ID(@CurrentDatabaseName)
END

IF @CurrentIsDatabaseAccessible IS NOT NULL
BEGIN
SET @DatabaseMessage = ‘Is accessible: ‘ + CASE WHEN @CurrentIsDatabaseAccessible = 1 THEN ‘Yes’ ELSE ‘No’ END
RAISERROR(‘%s’,10,1,@DatabaseMessage) WITH NOWAIT
END

IF @CurrentAvailabilityGroup IS NOT NULL
BEGIN
SET @DatabaseMessage = ‘Availability group: ‘ + ISNULL(@CurrentAvailabilityGroup,’N/A’)
RAISERROR(‘%s’,10,1,@DatabaseMessage) WITH NOWAIT

SET @DatabaseMessage = ‘Availability group role: ‘ + ISNULL(@CurrentAvailabilityGroupRole,’N/A’)
RAISERROR(‘%s’,10,1,@DatabaseMessage) WITH NOWAIT
END

IF @CurrentDatabaseMirroringRole IS NOT NULL
BEGIN
SET @DatabaseMessage = ‘Database mirroring role: ‘ + @CurrentDatabaseMirroringRole
RAISERROR(‘%s’,10,1,@DatabaseMessage) WITH NOWAIT
END

RAISERROR(@EmptyLine,10,1) WITH NOWAIT

IF @CurrentExecuteAsUserExists = 0
BEGIN
SET @DatabaseMessage = ‘The user ‘ + QUOTENAME(@ExecuteAsUser) + ‘ does not exist in the database ‘ + QUOTENAME(@CurrentDatabaseName) + ‘.’
RAISERROR(‘%s’,16,1,@DatabaseMessage) WITH NOWAIT
RAISERROR(@EmptyLine,10,1) WITH NOWAIT
END

IF @CurrentDatabaseState = ‘ONLINE’
AND NOT (@CurrentUserAccess = ‘SINGLE_USER’ AND @CurrentIsDatabaseAccessible = 0)
AND DATABASEPROPERTYEX(@CurrentDatabaseName,’Updateability’) = ‘READ_WRITE’
AND (@CurrentExecuteAsUserExists = 1 OR @CurrentExecuteAsUserExists IS NULL)
BEGIN

— Select indexes in the current database
IF (EXISTS(SELECT * FROM @ActionsPreferred) OR @UpdateStatistics IS NOT NULL) AND (SYSDATETIME() = 12 THEN ‘tables.is_memory_optimized’ ELSE ‘0’ END + ‘ AS IsMemoryOptimized’
+ ‘, indexes.index_id AS IndexID’
+ ‘, indexes.[name] AS IndexName’
+ ‘, indexes.[type] AS IndexType’
+ ‘, indexes.allow_page_locks AS AllowPageLocks’

+ ‘, CASE WHEN indexes.[type] = 1 AND EXISTS(SELECT * FROM sys.columns columns INNER JOIN sys.types types ON columns.system_type_id = types.user_type_id WHERE columns.[object_id] = objects.object_id AND types.name IN(”image”,”text”,”ntext”)) THEN 1 ELSE 0 END AS IsImageText’

+ ‘, CASE WHEN indexes.[type] = 1 AND EXISTS(SELECT * FROM sys.columns columns INNER JOIN 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] = objects.object_id 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))) THEN 1’
+ ‘ WHEN indexes.[type] = 2 AND EXISTS(SELECT * FROM sys.index_columns index_columns INNER JOIN sys.columns columns ON index_columns.[object_id] = columns.[object_id] AND index_columns.column_id = columns.column_id INNER JOIN 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] = objects.object_id AND index_columns.index_id = indexes.index_id 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))) THEN 1 ELSE 0 END AS IsNewLOB’

+ ‘, CASE WHEN indexes.[type] = 1 AND EXISTS(SELECT * FROM sys.columns columns WHERE columns.[object_id] = objects.object_id AND columns.is_filestream = 1) THEN 1 ELSE 0 END AS IsFileStream’

+ ‘, CASE WHEN EXISTS(SELECT * FROM sys.indexes indexes WHERE indexes.[object_id] = objects.object_id AND [type] IN(5,6)) THEN 1 ELSE 0 END AS IsColumnStore’

+ ‘, CASE WHEN EXISTS(SELECT * FROM sys.index_columns index_columns INNER JOIN sys.columns columns ON index_columns.object_id = columns.object_id AND index_columns.column_id = columns.column_id WHERE (index_columns.key_ordinal > 0 OR index_columns.partition_ordinal > 0) AND columns.is_computed = 1 AND index_columns.object_id = indexes.object_id AND index_columns.index_id = indexes.index_id) THEN 1 ELSE 0 END AS IsComputed’

+ ‘, CASE WHEN EXISTS(SELECT * FROM sys.index_columns index_columns INNER JOIN sys.columns columns ON index_columns.[object_id] = columns.[object_id] AND index_columns.column_id = columns.column_id INNER JOIN sys.types types ON columns.system_type_id = types.system_type_id WHERE index_columns.[object_id] = objects.object_id AND index_columns.index_id = indexes.index_id AND types.[name] = ”timestamp”) THEN 1 ELSE 0 END AS IsTimestamp’

+ ‘, CASE WHEN EXISTS (SELECT * FROM sys.indexes indexes2 INNER JOIN sys.destination_data_spaces destination_data_spaces ON indexes.data_space_id = destination_data_spaces.partition_scheme_id INNER JOIN sys.filegroups filegroups ON destination_data_spaces.data_space_id = filegroups.data_space_id WHERE filegroups.is_read_only = 1 AND indexes2.[object_id] = indexes.[object_id] AND indexes2.[index_id] = indexes.index_id’ + CASE WHEN @PartitionLevel = ‘Y’ THEN ‘ AND destination_data_spaces.destination_id = partitions.partition_number’ ELSE ” END + ‘) THEN 1’
+ ‘ WHEN EXISTS (SELECT * FROM sys.indexes indexes2 INNER JOIN sys.filegroups filegroups ON indexes.data_space_id = filegroups.data_space_id WHERE filegroups.is_read_only = 1 AND indexes.[object_id] = indexes2.[object_id] AND indexes.[index_id] = indexes2.index_id) THEN 1’
+ ‘ WHEN indexes.[type] = 1 AND EXISTS (SELECT * FROM sys.tables tables INNER JOIN sys.filegroups filegroups ON tables.lob_data_space_id = filegroups.data_space_id WHERE filegroups.is_read_only = 1 AND tables.[object_id] = objects.[object_id]) THEN 1 ELSE 0 END AS OnReadOnlyFileGroup’

+ ‘, ‘ + CASE WHEN @Version >= 14 THEN ‘CASE WHEN EXISTS(SELECT * FROM sys.index_resumable_operations index_resumable_operations WHERE state_desc = ”PAUSED” AND index_resumable_operations.object_id = indexes.object_id AND index_resumable_operations.index_id = indexes.index_id AND (index_resumable_operations.partition_number = partitions.partition_number OR index_resumable_operations.partition_number IS NULL)) THEN 1 ELSE 0 END’ ELSE ‘0’ END + ‘ AS ResumableIndexOperation’

+ ‘, stats.stats_id AS StatisticsID’
+ ‘, stats.name AS StatisticsName’
+ ‘, stats.no_recompute AS NoRecompute’
+ ‘, ‘ + CASE WHEN @Version >= 12 THEN ‘stats.is_incremental’ ELSE ‘0’ END + ‘ AS IsIncremental’
+ ‘, ‘ + CASE WHEN @PartitionLevel = ‘Y’ THEN ‘partitions.partition_id AS PartitionID’ WHEN @PartitionLevel = ‘N’ THEN ‘NULL AS PartitionID’ END
+ ‘, ‘ + CASE WHEN @PartitionLevel = ‘Y’ THEN ‘partitions.partition_number AS PartitionNumber’ WHEN @PartitionLevel = ‘N’ THEN ‘NULL AS PartitionNumber’ END
+ ‘, ‘ + CASE WHEN @PartitionLevel = ‘Y’ THEN ‘IndexPartitions.partition_count AS PartitionCount’ WHEN @PartitionLevel = ‘N’ THEN ‘NULL AS PartitionCount’ END
+ ‘, 0 AS [Order]’
+ ‘, 0 AS Selected’
+ ‘, 0 AS Completed’
+ ‘ FROM sys.indexes indexes’
+ ‘ INNER JOIN sys.objects objects ON indexes.[object_id] = objects.[object_id]’
+ ‘ INNER JOIN sys.schemas schemas ON objects.[schema_id] = schemas.[schema_id]’
+ ‘ LEFT OUTER JOIN sys.tables tables ON objects.[object_id] = tables.[object_id]’
+ ‘ LEFT OUTER JOIN sys.stats stats ON indexes.[object_id] = stats.[object_id] AND indexes.[index_id] = stats.[stats_id]’
IF @PartitionLevel = ‘Y’
BEGIN
SET @CurrentCommand = @CurrentCommand + ‘ LEFT OUTER JOIN 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(DISTINCT partitions.partition_number) AS partition_count FROM 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]’
END

SET @CurrentCommand = @CurrentCommand + ‘ WHERE objects.[type] IN(”U”,”V”)’
+ CASE WHEN @MSShippedObjects = ‘N’ THEN ‘ AND objects.is_ms_shipped = 0’ ELSE ” END
+ ‘ AND indexes.[type] IN(1,2,3,4,5,6,7)’
+ ‘ AND indexes.is_disabled = 0 AND indexes.is_hypothetical = 0’
END

IF (EXISTS(SELECT * FROM @ActionsPreferred) AND @UpdateStatistics = ‘COLUMNS’) OR @UpdateStatistics = ‘ALL’
BEGIN
SET @CurrentCommand = @CurrentCommand + ‘ UNION ‘
END

IF @UpdateStatistics IN(‘ALL’,’COLUMNS’)
BEGIN
SET @CurrentCommand = @CurrentCommand + ‘SELECT schemas.[schema_id] AS SchemaID’
+ ‘, schemas.[name] AS SchemaName’
+ ‘, objects.[object_id] AS ObjectID’
+ ‘, objects.[name] AS ObjectName’
+ ‘, RTRIM(objects.[type]) AS ObjectType’
+ ‘, ‘ + CASE WHEN @Version >= 12 THEN ‘tables.is_memory_optimized’ ELSE ‘0’ END + ‘ AS IsMemoryOptimized’
+ ‘, NULL AS IndexID, NULL AS IndexName’
+ ‘, NULL AS IndexType’
+ ‘, NULL AS AllowPageLocks’
+ ‘, NULL AS IsImageText’
+ ‘, NULL AS IsNewLOB’
+ ‘, NULL AS IsFileStream’
+ ‘, NULL AS IsColumnStore’
+ ‘, NULL AS IsComputed’
+ ‘, NULL AS IsTimestamp’
+ ‘, NULL AS OnReadOnlyFileGroup’
+ ‘, NULL AS ResumableIndexOperation’
+ ‘, stats.stats_id AS StatisticsID’
+ ‘, stats.name AS StatisticsName’
+ ‘, stats.no_recompute AS NoRecompute’
+ ‘, ‘ + CASE WHEN @Version >= 12 THEN ‘stats.is_incremental’ ELSE ‘0’ END + ‘ AS IsIncremental’
+ ‘, NULL AS PartitionID’
+ ‘, ‘ + CASE WHEN @PartitionLevelStatistics = 1 THEN ‘dm_db_incremental_stats_properties.partition_number’ ELSE ‘NULL’ END + ‘ AS PartitionNumber’
+ ‘, NULL AS PartitionCount’
+ ‘, 0 AS [Order]’
+ ‘, 0 AS Selected’
+ ‘, 0 AS Completed’
+ ‘ FROM sys.stats stats’
+ ‘ INNER JOIN sys.objects objects ON stats.[object_id] = objects.[object_id]’
+ ‘ INNER JOIN sys.schemas schemas ON objects.[schema_id] = schemas.[schema_id]’
+ ‘ LEFT OUTER JOIN sys.tables tables ON objects.[object_id] = tables.[object_id]’

IF @PartitionLevelStatistics = 1
BEGIN
SET @CurrentCommand = @CurrentCommand + ‘ OUTER APPLY sys.dm_db_incremental_stats_properties(stats.object_id, stats.stats_id) dm_db_incremental_stats_properties’
END

SET @CurrentCommand = @CurrentCommand + ‘ WHERE objects.[type] IN(”U”,”V”)’
+ CASE WHEN @MSShippedObjects = ‘N’ THEN ‘ AND objects.is_ms_shipped = 0’ ELSE ” END
+ ‘ AND NOT EXISTS(SELECT * FROM sys.indexes indexes WHERE indexes.[object_id] = stats.[object_id] AND indexes.index_id = stats.stats_id)’
END

SET @CurrentCommand = @CurrentCommand + ‘) IndexesStatistics’

INSERT INTO @tmpIndexesStatistics (SchemaID, SchemaName, ObjectID, ObjectName, ObjectType, IsMemoryOptimized, IndexID, IndexName, IndexType, AllowPageLocks, IsImageText, IsNewLOB, IsFileStream, IsColumnStore, IsComputed, IsTimestamp, OnReadOnlyFileGroup, ResumableIndexOperation, StatisticsID, StatisticsName, [NoRecompute], IsIncremental, PartitionID, PartitionNumber, PartitionCount, [Order], Selected, Completed)
EXECUTE @CurrentDatabase_sp_executesql @stmt = @CurrentCommand
SET @Error = @@ERROR
IF @Error 0
BEGIN
SET @ReturnCode = @Error
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

UPDATE tmpIndexesStatistics
SET tmpIndexesStatistics.StartPosition = SelectedIndexes2.StartPosition
FROM @tmpIndexesStatistics tmpIndexesStatistics
INNER JOIN (SELECT tmpIndexesStatistics.SchemaName, tmpIndexesStatistics.ObjectName, tmpIndexesStatistics.IndexName, tmpIndexesStatistics.StatisticsName, MIN(SelectedIndexes.StartPosition) AS StartPosition
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
GROUP BY tmpIndexesStatistics.SchemaName, tmpIndexesStatistics.ObjectName, tmpIndexesStatistics.IndexName, tmpIndexesStatistics.StatisticsName) SelectedIndexes2
ON tmpIndexesStatistics.SchemaName = SelectedIndexes2.SchemaName
AND tmpIndexesStatistics.ObjectName = SelectedIndexes2.ObjectName
AND (tmpIndexesStatistics.IndexName = SelectedIndexes2.IndexName OR tmpIndexesStatistics.IndexName IS NULL)
AND (tmpIndexesStatistics.StatisticsName = SelectedIndexes2.StatisticsName OR tmpIndexesStatistics.StatisticsName IS NULL)
END;

WITH tmpIndexesStatistics AS (
SELECT SchemaName, ObjectName, [Order], ROW_NUMBER() OVER (ORDER BY ISNULL(ResumableIndexOperation,0) DESC, StartPosition ASC, SchemaName ASC, ObjectName ASC, CASE WHEN IndexType IS NULL THEN 1 ELSE 0 END ASC, IndexType ASC, IndexName ASC, StatisticsName ASC, PartitionNumber ASC) AS RowNumber
FROM @tmpIndexesStatistics tmpIndexesStatistics
WHERE Selected = 1
)
UPDATE tmpIndexesStatistics
SET [Order] = RowNumber

SET @ErrorMessage = ”
SELECT @ErrorMessage = @ErrorMessage + QUOTENAME(DatabaseName) + ‘.’ + QUOTENAME(SchemaName) + ‘.’ + QUOTENAME(ObjectName) + ‘, ‘
FROM @SelectedIndexes SelectedIndexes
WHERE DatabaseName = @CurrentDatabaseName
AND SchemaName NOT LIKE ‘%[%]%’
AND ObjectName NOT LIKE ‘%[%]%’
AND IndexName LIKE ‘%[%]%’
AND NOT EXISTS (SELECT * FROM @tmpIndexesStatistics WHERE SchemaName = SelectedIndexes.SchemaName AND ObjectName = SelectedIndexes.ObjectName)
IF @@ROWCOUNT > 0
BEGIN
SET @ErrorMessage = ‘The following objects in the @Indexes parameter do not exist: ‘ + LEFT(@ErrorMessage,LEN(@ErrorMessage)-1) + ‘.’
RAISERROR(‘%s’,10,1,@ErrorMessage) WITH NOWAIT
SET @Error = @@ERROR
RAISERROR(@EmptyLine,10,1) WITH NOWAIT
END

SET @ErrorMessage = ”
SELECT @ErrorMessage = @ErrorMessage + QUOTENAME(DatabaseName) + QUOTENAME(SchemaName) + ‘.’ + QUOTENAME(ObjectName) + ‘.’ + QUOTENAME(IndexName) + ‘, ‘
FROM @SelectedIndexes SelectedIndexes
WHERE DatabaseName = @CurrentDatabaseName
AND SchemaName NOT LIKE ‘%[%]%’
AND ObjectName NOT LIKE ‘%[%]%’
AND IndexName NOT LIKE ‘%[%]%’
AND NOT EXISTS (SELECT * FROM @tmpIndexesStatistics WHERE SchemaName = SelectedIndexes.SchemaName AND ObjectName = SelectedIndexes.ObjectName AND IndexName = SelectedIndexes.IndexName)
IF @@ROWCOUNT > 0
BEGIN
SET @ErrorMessage = ‘The following indexes in the @Indexes parameter do not exist: ‘ + LEFT(@ErrorMessage,LEN(@ErrorMessage)-1) + ‘.’
RAISERROR(‘%s’,10,1,@ErrorMessage) WITH NOWAIT
SET @Error = @@ERROR
RAISERROR(@EmptyLine,10,1) WITH NOWAIT
END

WHILE (SYSDATETIME() = 10.504000 AND @Version = 11.03000
BEGIN
SET @CurrentCommand += ‘SELECT @ParamRowCount = [rows], @ParamModificationCounter = modification_counter FROM sys.dm_db_stats_properties (@ParamObjectID, @ParamStatisticsID)’
END
ELSE
BEGIN
SET @CurrentCommand += ‘SELECT @ParamRowCount = rowcnt, @ParamModificationCounter = rowmodctr FROM sys.sysindexes sysindexes WHERE sysindexes.[id] = @ParamObjectID AND sysindexes.[indid] = @ParamStatisticsID’
END

BEGIN TRY
EXECUTE @CurrentDatabase_sp_executesql @stmt = @CurrentCommand, @params = N’@ParamObjectID int, @ParamStatisticsID int, @ParamPartitionNumber int, @ParamRowCount bigint OUTPUT, @ParamModificationCounter bigint OUTPUT’, @ParamObjectID = @CurrentObjectID, @ParamStatisticsID = @CurrentStatisticsID, @ParamPartitionNumber = @CurrentPartitionNumber, @ParamRowCount = @CurrentRowCount OUTPUT, @ParamModificationCounter = @CurrentModificationCounter OUTPUT

IF @CurrentRowCount IS NULL SET @CurrentRowCount = 0
IF @CurrentModificationCounter IS NULL SET @CurrentModificationCounter = 0
END TRY
BEGIN CATCH
SET @ErrorMessage = ‘Msg ‘ + CAST(ERROR_NUMBER() AS nvarchar) + ‘, ‘ + ISNULL(ERROR_MESSAGE(),”) + CASE WHEN ERROR_NUMBER() = 1222 THEN ‘ The statistics ‘ + QUOTENAME(@CurrentStatisticsName) + ‘ on the object ‘ + QUOTENAME(@CurrentDatabaseName) + ‘.’ + QUOTENAME(@CurrentSchemaName) + ‘.’ + QUOTENAME(@CurrentObjectName) + ‘ is locked. The rows and modification_counter could not be checked.’ ELSE ” END
SET @Severity = CASE WHEN ERROR_NUMBER() IN(1205,1222) THEN @LockMessageSeverity ELSE 16 END
RAISERROR(‘%s’,@Severity,1,@ErrorMessage) WITH NOWAIT
RAISERROR(@EmptyLine,10,1) WITH NOWAIT

IF NOT (ERROR_NUMBER() IN(1205,1222) AND @LockMessageSeverity = 10)
BEGIN
SET @ReturnCode = ERROR_NUMBER()
END

GOTO NoAction
END CATCH
END

— Is the index fragmented?
IF @CurrentIndexID IS NOT NULL
AND @CurrentOnReadOnlyFileGroup = 0
AND EXISTS(SELECT * FROM @ActionsPreferred)
AND (EXISTS(SELECT [Priority], [Action], COUNT(*) FROM @ActionsPreferred GROUP BY [Priority], [Action] HAVING COUNT(*) 3) OR @MinNumberOfPages > 0 OR @MaxNumberOfPages IS NOT NULL)
BEGIN
SET @CurrentCommand = ”

IF @LockTimeout IS NOT NULL SET @CurrentCommand = ‘SET LOCK_TIMEOUT ‘ + CAST(@LockTimeout * 1000 AS nvarchar) + ‘; ‘

SET @CurrentCommand += ‘SELECT @ParamFragmentationLevel = MAX(avg_fragmentation_in_percent), @ParamPageCount = SUM(page_count) FROM sys.dm_db_index_physical_stats(DB_ID(@ParamDatabaseName), @ParamObjectID, @ParamIndexID, @ParamPartitionNumber, ”LIMITED”) WHERE alloc_unit_type_desc = ”IN_ROW_DATA” AND index_level = 0’

BEGIN TRY
EXECUTE sp_executesql @stmt = @CurrentCommand, @params = N’@ParamDatabaseName nvarchar(max), @ParamObjectID int, @ParamIndexID int, @ParamPartitionNumber int, @ParamFragmentationLevel float OUTPUT, @ParamPageCount bigint OUTPUT’, @ParamDatabaseName = @CurrentDatabaseName, @ParamObjectID = @CurrentObjectID, @ParamIndexID = @CurrentIndexID, @ParamPartitionNumber = @CurrentPartitionNumber, @ParamFragmentationLevel = @CurrentFragmentationLevel OUTPUT, @ParamPageCount = @CurrentPageCount OUTPUT
END TRY
BEGIN CATCH
SET @ErrorMessage = ‘Msg ‘ + CAST(ERROR_NUMBER() AS nvarchar) + ‘, ‘ + ISNULL(ERROR_MESSAGE(),”) + CASE WHEN ERROR_NUMBER() = 1222 THEN ‘ The index ‘ + QUOTENAME(@CurrentIndexName) + ‘ on the object ‘ + QUOTENAME(@CurrentDatabaseName) + ‘.’ + QUOTENAME(@CurrentSchemaName) + ‘.’ + QUOTENAME(@CurrentObjectName) + ‘ is locked. The page_count and avg_fragmentation_in_percent could not be checked.’ ELSE ” END
SET @Severity = CASE WHEN ERROR_NUMBER() IN(1205,1222) THEN @LockMessageSeverity ELSE 16 END
RAISERROR(‘%s’,@Severity,1,@ErrorMessage) WITH NOWAIT
RAISERROR(@EmptyLine,10,1) WITH NOWAIT

IF NOT (ERROR_NUMBER() IN(1205,1222) AND @LockMessageSeverity = 10)
BEGIN
SET @ReturnCode = ERROR_NUMBER()
END

GOTO NoAction
END CATCH
END

— Select fragmentation group
IF @CurrentIndexID IS NOT NULL AND @CurrentOnReadOnlyFileGroup = 0 AND EXISTS(SELECT * FROM @ActionsPreferred)
BEGIN
SET @CurrentFragmentationGroup = CASE
WHEN @CurrentFragmentationLevel >= @FragmentationLevel2 THEN ‘High’
WHEN @CurrentFragmentationLevel >= @FragmentationLevel1 AND @CurrentFragmentationLevel < @FragmentationLevel2 THEN 'Medium'
WHEN @CurrentFragmentationLevel = 12)
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 (@CurrentIsColumnStore = 0 OR @Version = @MinNumberOfPages OR @MinNumberOfPages = 0)
AND (@CurrentPageCount <= @MaxNumberOfPages OR @MaxNumberOfPages IS NULL)
AND @CurrentResumableIndexOperation = 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

IF @CurrentResumableIndexOperation = 1
BEGIN
SET @CurrentAction = ‘INDEX_REBUILD_ONLINE’
END

— Workaround for limitation in SQL Server, http://support.microsoft.com/kb/2292737
IF @CurrentIndexID IS NOT NULL
BEGIN
SET @CurrentMaxDOP = @MaxDOP

IF @CurrentAction = ‘INDEX_REBUILD_ONLINE’ AND @CurrentAllowPageLocks = 0
BEGIN
SET @CurrentMaxDOP = 1
END
END

— Update statistics?
IF @CurrentStatisticsID IS NOT NULL
AND ((@UpdateStatistics = ‘ALL’ AND (@CurrentIndexType IN (1,2,3,4,7) OR @CurrentIndexID IS NULL)) OR (@UpdateStatistics = ‘INDEX’ AND @CurrentIndexID IS NOT NULL AND @CurrentIndexType IN (1,2,3,4,7)) OR (@UpdateStatistics = ‘COLUMNS’ AND @CurrentIndexID IS NULL))
AND ((@OnlyModifiedStatistics = ‘N’ AND @StatisticsModificationLevel IS NULL) OR (@OnlyModifiedStatistics = ‘Y’ AND @CurrentModificationCounter > 0) OR ((@CurrentModificationCounter * 1. / NULLIF(@CurrentRowCount,0)) * 100 >= @StatisticsModificationLevel) OR (@StatisticsModificationLevel IS NOT NULL AND @CurrentModificationCounter > 0 AND (@CurrentModificationCounter >= SQRT(@CurrentRowCount * 1000))) OR (@CurrentIsMemoryOptimized = 1 AND NOT (@Version >= 13 OR SERVERPROPERTY(‘EngineEdition’) IN (5,8))))
AND ((@CurrentIsPartition = 0 AND (@CurrentAction NOT IN(‘INDEX_REBUILD_ONLINE’,’INDEX_REBUILD_OFFLINE’) OR @CurrentAction IS NULL)) OR (@CurrentIsPartition = 1 AND (@CurrentPartitionNumber = @CurrentPartitionCount OR (@PartitionLevelStatistics = 1 AND @CurrentIsIncremental = 1))))
BEGIN
SET @CurrentUpdateStatistics = ‘Y’
END
ELSE
BEGIN
SET @CurrentUpdateStatistics = ‘N’
END

SET @CurrentStatisticsSample = @StatisticsSample
SET @CurrentStatisticsResample = @StatisticsResample

— Memory-optimized tables only supports FULLSCAN and RESAMPLE in SQL Server 2014
IF @CurrentIsMemoryOptimized = 1 AND NOT (@Version >= 13 OR SERVERPROPERTY(‘EngineEdition’) IN (5,8)) AND (@CurrentStatisticsSample 100 OR @CurrentStatisticsSample IS NULL)
BEGIN
SET @CurrentStatisticsSample = NULL
SET @CurrentStatisticsResample = ‘Y’
END

— Incremental statistics only supports RESAMPLE
IF @PartitionLevelStatistics = 1 AND @CurrentIsIncremental = 1
BEGIN
SET @CurrentStatisticsSample = NULL
SET @CurrentStatisticsResample = ‘Y’
END

— Create index 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 += ‘IndexType: ‘ + CASE WHEN @CurrentIndexType = 1 THEN ‘Clustered’ WHEN @CurrentIndexType = 2 THEN ‘NonClustered’ WHEN @CurrentIndexType = 3 THEN ‘XML’ WHEN @CurrentIndexType = 4 THEN ‘Spatial’ WHEN @CurrentIndexType = 5 THEN ‘Clustered Columnstore’ WHEN @CurrentIndexType = 6 THEN ‘NonClustered Columnstore’ WHEN @CurrentIndexType = 7 THEN ‘NonClustered Hash’ ELSE ‘N/A’ END + ‘, ‘
SET @CurrentComment += ‘ImageText: ‘ + CASE WHEN @CurrentIsImageText = 1 THEN ‘Yes’ WHEN @CurrentIsImageText = 0 THEN ‘No’ ELSE ‘N/A’ END + ‘, ‘
SET @CurrentComment += ‘NewLOB: ‘ + CASE WHEN @CurrentIsNewLOB = 1 THEN ‘Yes’ WHEN @CurrentIsNewLOB = 0 THEN ‘No’ ELSE ‘N/A’ END + ‘, ‘
SET @CurrentComment += ‘FileStream: ‘ + CASE WHEN @CurrentIsFileStream = 1 THEN ‘Yes’ WHEN @CurrentIsFileStream = 0 THEN ‘No’ ELSE ‘N/A’ END + ‘, ‘
IF @Version >= 11 SET @CurrentComment += ‘ColumnStore: ‘ + CASE WHEN @CurrentIsColumnStore = 1 THEN ‘Yes’ WHEN @CurrentIsColumnStore = 0 THEN ‘No’ ELSE ‘N/A’ END + ‘, ‘
IF @Version >= 14 AND @Resumable = ‘Y’ SET @CurrentComment += ‘Computed: ‘ + CASE WHEN @CurrentIsComputed = 1 THEN ‘Yes’ WHEN @CurrentIsComputed = 0 THEN ‘No’ ELSE ‘N/A’ END + ‘, ‘
IF @Version >= 14 AND @Resumable = ‘Y’ SET @CurrentComment += ‘Timestamp: ‘ + CASE WHEN @CurrentIsTimestamp = 1 THEN ‘Yes’ WHEN @CurrentIsTimestamp = 0 THEN ‘No’ ELSE ‘N/A’ END + ‘, ‘
SET @CurrentComment += ‘AllowPageLocks: ‘ + CASE WHEN @CurrentAllowPageLocks = 1 THEN ‘Yes’ WHEN @CurrentAllowPageLocks = 0 THEN ‘No’ ELSE ‘N/A’ END + ‘, ‘
SET @CurrentComment += ‘PageCount: ‘ + ISNULL(CAST(@CurrentPageCount AS nvarchar),’N/A’) + ‘, ‘
SET @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 RAW(‘ExtendedInfo’), ELEMENTS)
END

IF @CurrentIndexID IS NOT NULL AND @CurrentAction IS NOT NULL AND (SYSDATETIME() = 12) AND @CurrentResumableIndexOperation = 0
BEGIN
INSERT INTO @CurrentAlterIndexWithClauseArguments (Argument)
SELECT ‘ONLINE = ON’ + CASE WHEN @WaitAtLowPriorityMaxDuration IS NOT NULL THEN ‘ (WAIT_AT_LOW_PRIORITY (MAX_DURATION = ‘ + CAST(@WaitAtLowPriorityMaxDuration AS nvarchar) + ‘, ABORT_AFTER_WAIT = ‘ + UPPER(@WaitAtLowPriorityAbortAfterWait) + ‘))’ ELSE ” END
END

IF @CurrentAction = ‘INDEX_REBUILD_OFFLINE’ AND (@CurrentIsPartition = 0 OR @Version >= 12) AND @CurrentResumableIndexOperation = 0
BEGIN
INSERT INTO @CurrentAlterIndexWithClauseArguments (Argument)
SELECT ‘ONLINE = OFF’
END

IF @CurrentAction IN(‘INDEX_REBUILD_ONLINE’,’INDEX_REBUILD_OFFLINE’) AND @CurrentMaxDOP IS NOT NULL
BEGIN
INSERT INTO @CurrentAlterIndexWithClauseArguments (Argument)
SELECT ‘MAXDOP = ‘ + CAST(@CurrentMaxDOP AS nvarchar)
END

IF @CurrentAction IN(‘INDEX_REBUILD_ONLINE’,’INDEX_REBUILD_OFFLINE’) AND @FillFactor IS NOT NULL AND @CurrentIsPartition = 0 AND @CurrentIndexType IN(1,2,3,4) AND @CurrentResumableIndexOperation = 0
BEGIN
INSERT INTO @CurrentAlterIndexWithClauseArguments (Argument)
SELECT ‘FILLFACTOR = ‘ + CAST(@FillFactor AS nvarchar)
END

IF @CurrentAction IN(‘INDEX_REBUILD_ONLINE’,’INDEX_REBUILD_OFFLINE’) AND @PadIndex = ‘Y’ AND @CurrentIsPartition = 0 AND @CurrentIndexType IN(1,2,3,4) AND @CurrentResumableIndexOperation = 0
BEGIN
INSERT INTO @CurrentAlterIndexWithClauseArguments (Argument)
SELECT ‘PAD_INDEX = ON’
END

IF (@Version >= 14 OR SERVERPROPERTY(‘EngineEdition’) IN (5,8)) AND @CurrentAction = ‘INDEX_REBUILD_ONLINE’ AND @CurrentResumableIndexOperation = 0
BEGIN
INSERT INTO @CurrentAlterIndexWithClauseArguments (Argument)
SELECT CASE WHEN @Resumable = ‘Y’ AND @CurrentIndexType IN(1,2) AND @CurrentIsComputed = 0 AND @CurrentIsTimestamp = 0 THEN ‘RESUMABLE = ON’ ELSE ‘RESUMABLE = OFF’ END
END

IF (@Version >= 14 OR SERVERPROPERTY(‘EngineEdition’) IN (5,8)) AND @CurrentAction = ‘INDEX_REBUILD_ONLINE’ AND @CurrentResumableIndexOperation = 0 AND @Resumable = ‘Y’ AND @CurrentIndexType IN(1,2) AND @CurrentIsComputed = 0 AND @CurrentIsTimestamp = 0 AND @TimeLimit IS NOT NULL
BEGIN
INSERT INTO @CurrentAlterIndexWithClauseArguments (Argument)
SELECT ‘MAX_DURATION = ‘ + CAST(DATEDIFF(MINUTE,SYSDATETIME(),DATEADD(SECOND,@TimeLimit,@StartTime)) AS nvarchar(max))
END

IF @CurrentAction IN(‘INDEX_REORGANIZE’) AND @LOBCompaction = ‘Y’
BEGIN
INSERT INTO @CurrentAlterIndexWithClauseArguments (Argument)
SELECT ‘LOB_COMPACTION = ON’
END

IF @CurrentAction IN(‘INDEX_REORGANIZE’) AND @LOBCompaction = ‘N’
BEGIN
INSERT INTO @CurrentAlterIndexWithClauseArguments (Argument)
SELECT ‘LOB_COMPACTION = OFF’
END

IF EXISTS (SELECT * FROM @CurrentAlterIndexWithClauseArguments)
BEGIN
SET @CurrentAlterIndexWithClause = ‘ WITH (‘

WHILE (1 = 1)
BEGIN
SELECT TOP 1 @CurrentAlterIndexArgumentID = ID,
@CurrentAlterIndexArgument = Argument
FROM @CurrentAlterIndexWithClauseArguments
WHERE Added = 0
ORDER BY ID ASC

IF @@ROWCOUNT = 0
BEGIN
BREAK
END

SET @CurrentAlterIndexWithClause += @CurrentAlterIndexArgument + ‘, ‘

UPDATE @CurrentAlterIndexWithClauseArguments
SET Added = 1
WHERE [ID] = @CurrentAlterIndexArgumentID
END

SET @CurrentAlterIndexWithClause = RTRIM(@CurrentAlterIndexWithClause)

SET @CurrentAlterIndexWithClause = LEFT(@CurrentAlterIndexWithClause,LEN(@CurrentAlterIndexWithClause) – 1)

SET @CurrentAlterIndexWithClause = @CurrentAlterIndexWithClause + ‘)’
END

IF @CurrentAlterIndexWithClause IS NOT NULL SET @CurrentCommand += @CurrentAlterIndexWithClause

EXECUTE @CurrentCommandOutput = dbo.CommandExecute @DatabaseContext = @CurrentDatabaseName, @Command = @CurrentCommand, @CommandType = @CurrentCommandType, @Mode = 2, @Comment = @CurrentComment, @DatabaseName = @CurrentDatabaseName, @SchemaName = @CurrentSchemaName, @ObjectName = @CurrentObjectName, @ObjectType = @CurrentObjectType, @IndexName = @CurrentIndexName, @IndexType = @CurrentIndexType, @PartitionNumber = @CurrentPartitionNumber, @ExtendedInfo = @CurrentExtendedInfo, @LockMessageSeverity = @LockMessageSeverity, @ExecuteAsUser = @ExecuteAsUser, @LogToTable = @LogToTable, @Execute = @Execute
SET @Error = @@ERROR
IF @Error 0 SET @CurrentCommandOutput = @Error
IF @CurrentCommandOutput 0 SET @ReturnCode = @CurrentCommandOutput

IF @Delay > 0
BEGIN
SET @CurrentDelay = DATEADD(ss,@Delay,’1900-01-01′)
WAITFOR DELAY @CurrentDelay
END
END

SET @CurrentMaxDOP = @MaxDOP

— Create statistics comment
IF @CurrentStatisticsID IS NOT NULL
BEGIN
SET @CurrentComment = ‘ObjectType: ‘ + CASE WHEN @CurrentObjectType = ‘U’ THEN ‘Table’ WHEN @CurrentObjectType = ‘V’ THEN ‘View’ ELSE ‘N/A’ END + ‘, ‘
SET @CurrentComment += ‘IndexType: ‘ + CASE WHEN @CurrentIndexID IS NOT NULL THEN ‘Index’ ELSE ‘Column’ END + ‘, ‘
IF @CurrentIndexID IS NOT NULL SET @CurrentComment += ‘IndexType: ‘ + CASE WHEN @CurrentIndexType = 1 THEN ‘Clustered’ WHEN @CurrentIndexType = 2 THEN ‘NonClustered’ WHEN @CurrentIndexType = 3 THEN ‘XML’ WHEN @CurrentIndexType = 4 THEN ‘Spatial’ WHEN @CurrentIndexType = 5 THEN ‘Clustered Columnstore’ WHEN @CurrentIndexType = 6 THEN ‘NonClustered Columnstore’ WHEN @CurrentIndexType = 7 THEN ‘NonClustered Hash’ ELSE ‘N/A’ END + ‘, ‘
SET @CurrentComment += ‘Incremental: ‘ + CASE WHEN @CurrentIsIncremental = 1 THEN ‘Y’ WHEN @CurrentIsIncremental = 0 THEN ‘N’ ELSE ‘N/A’ END + ‘, ‘
SET @CurrentComment += ‘RowCount: ‘ + ISNULL(CAST(@CurrentRowCount AS nvarchar),’N/A’) + ‘, ‘
SET @CurrentComment += ‘ModificationCounter: ‘ + ISNULL(CAST(@CurrentModificationCounter AS nvarchar),’N/A’)
END

IF @CurrentStatisticsID IS NOT NULL AND (@CurrentRowCount IS NOT NULL OR @CurrentModificationCounter IS NOT NULL)
BEGIN
SET @CurrentExtendedInfo = (SELECT *
FROM (SELECT CAST(@CurrentRowCount AS nvarchar) AS [RowCount],
CAST(@CurrentModificationCounter AS nvarchar) AS ModificationCounter
) ExtendedInfo FOR XML RAW(‘ExtendedInfo’), ELEMENTS)
END

IF @CurrentStatisticsID IS NOT NULL AND @CurrentUpdateStatistics = ‘Y’ AND (SYSDATETIME() = 12.06024 AND @Version = 13.05026 AND @Version = 14.030154)
BEGIN
INSERT INTO @CurrentUpdateStatisticsWithClauseArguments (Argument)
SELECT ‘MAXDOP = ‘ + CAST(@CurrentMaxDOP AS nvarchar)
END

IF @CurrentStatisticsSample = 100
BEGIN
INSERT INTO @CurrentUpdateStatisticsWithClauseArguments (Argument)
SELECT ‘FULLSCAN’
END

IF @CurrentStatisticsSample IS NOT NULL AND @CurrentStatisticsSample 100
BEGIN
INSERT INTO @CurrentUpdateStatisticsWithClauseArguments (Argument)
SELECT ‘SAMPLE ‘ + CAST(@CurrentStatisticsSample AS nvarchar) + ‘ PERCENT’
END

IF @CurrentStatisticsResample = ‘Y’
BEGIN
INSERT INTO @CurrentUpdateStatisticsWithClauseArguments (Argument)
SELECT ‘RESAMPLE’
END

IF @CurrentNoRecompute = 1
BEGIN
INSERT INTO @CurrentUpdateStatisticsWithClauseArguments (Argument)
SELECT ‘NORECOMPUTE’
END

IF EXISTS (SELECT * FROM @CurrentUpdateStatisticsWithClauseArguments)
BEGIN
SET @CurrentUpdateStatisticsWithClause = ‘ WITH’

WHILE (1 = 1)
BEGIN
SELECT TOP 1 @CurrentUpdateStatisticsArgumentID = ID,
@CurrentUpdateStatisticsArgument = Argument
FROM @CurrentUpdateStatisticsWithClauseArguments
WHERE Added = 0
ORDER BY ID ASC

IF @@ROWCOUNT = 0
BEGIN
BREAK
END

SET @CurrentUpdateStatisticsWithClause = @CurrentUpdateStatisticsWithClause + ‘ ‘ + @CurrentUpdateStatisticsArgument + ‘,’

UPDATE @CurrentUpdateStatisticsWithClauseArguments
SET Added = 1
WHERE [ID] = @CurrentUpdateStatisticsArgumentID
END

SET @CurrentUpdateStatisticsWithClause = LEFT(@CurrentUpdateStatisticsWithClause,LEN(@CurrentUpdateStatisticsWithClause) – 1)
END

IF @CurrentUpdateStatisticsWithClause IS NOT NULL SET @CurrentCommand += @CurrentUpdateStatisticsWithClause

IF @PartitionLevelStatistics = 1 AND @CurrentIsIncremental = 1 AND @CurrentPartitionNumber IS NOT NULL SET @CurrentCommand += ‘ ON PARTITIONS(‘ + CAST(@CurrentPartitionNumber AS nvarchar(max)) + ‘)’

EXECUTE @CurrentCommandOutput = dbo.CommandExecute @DatabaseContext = @CurrentDatabaseName, @Command = @CurrentCommand, @CommandType = @CurrentCommandType, @Mode = 2, @Comment = @CurrentComment, @DatabaseName = @CurrentDatabaseName, @SchemaName = @CurrentSchemaName, @ObjectName = @CurrentObjectName, @ObjectType = @CurrentObjectType, @IndexName = @CurrentIndexName, @IndexType = @CurrentIndexType, @StatisticsName = @CurrentStatisticsName, @ExtendedInfo = @CurrentExtendedInfo, @LockMessageSeverity = @LockMessageSeverity, @ExecuteAsUser = @ExecuteAsUser, @LogToTable = @LogToTable, @Execute = @Execute
SET @Error = @@ERROR
IF @Error 0 SET @CurrentCommandOutput = @Error
IF @CurrentCommandOutput 0 SET @ReturnCode = @CurrentCommandOutput
END

NoAction:

— Update that the index or statistics is completed
UPDATE @tmpIndexesStatistics
SET Completed = 1
WHERE Selected = 1
AND Completed = 0
AND [Order] = @CurrentIxOrder
AND ID = @CurrentIxID

— Clear variables
SET @CurrentDatabaseContext = NULL

SET @CurrentCommand = NULL
SET @CurrentCommandOutput = NULL
SET @CurrentCommandType = NULL
SET @CurrentComment = NULL
SET @CurrentExtendedInfo = NULL

SET @CurrentIxID = NULL
SET @CurrentIxOrder = NULL
SET @CurrentSchemaID = NULL
SET @CurrentSchemaName = NULL
SET @CurrentObjectID = NULL
SET @CurrentObjectName = NULL
SET @CurrentObjectType = NULL
SET @CurrentIsMemoryOptimized = 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 @CurrentIsColumnStore = NULL
SET @CurrentIsComputed = NULL
SET @CurrentIsTimestamp = NULL
SET @CurrentAllowPageLocks = NULL
SET @CurrentNoRecompute = NULL
SET @CurrentIsIncremental = NULL
SET @CurrentRowCount = NULL
SET @CurrentModificationCounter = NULL
SET @CurrentOnReadOnlyFileGroup = NULL
SET @CurrentResumableIndexOperation = NULL
SET @CurrentFragmentationLevel = NULL
SET @CurrentPageCount = NULL
SET @CurrentFragmentationGroup = NULL
SET @CurrentAction = NULL
SET @CurrentMaxDOP = NULL
SET @CurrentUpdateStatistics = NULL
SET @CurrentStatisticsSample = NULL
SET @CurrentStatisticsResample = NULL
SET @CurrentAlterIndexArgumentID = NULL
SET @CurrentAlterIndexArgument = NULL
SET @CurrentAlterIndexWithClause = NULL
SET @CurrentUpdateStatisticsArgumentID = NULL
SET @CurrentUpdateStatisticsArgument = NULL
SET @CurrentUpdateStatisticsWithClause = NULL

DELETE FROM @CurrentActionsAllowed
DELETE FROM @CurrentAlterIndexWithClauseArguments
DELETE FROM @CurrentUpdateStatisticsWithClauseArguments

END

END

IF @CurrentDatabaseState = ‘SUSPECT’
BEGIN
SET @ErrorMessage = ‘The database ‘ + QUOTENAME(@CurrentDatabaseName) + ‘ is in a SUSPECT state.’
RAISERROR(‘%s’,16,1,@ErrorMessage) WITH NOWAIT
RAISERROR(@EmptyLine,10,1) WITH NOWAIT
SET @Error = @@ERROR
END

— Update that the database is completed
IF @DatabasesInParallel = ‘Y’
BEGIN
UPDATE dbo.QueueDatabase
SET DatabaseEndTime = SYSDATETIME()
WHERE QueueID = @QueueID
AND DatabaseName = @CurrentDatabaseName
END
ELSE
BEGIN
UPDATE @tmpDatabases
SET Completed = 1
WHERE Selected = 1
AND Completed = 0
AND ID = @CurrentDBID
END

— Clear variables
SET @CurrentDBID = NULL
SET @CurrentDatabaseName = NULL

SET @CurrentDatabase_sp_executesql = NULL

SET @CurrentExecuteAsUserExists = NULL
SET @CurrentUserAccess = NULL
SET @CurrentIsReadOnly = NULL
SET @CurrentDatabaseState = NULL
SET @CurrentInStandby = NULL
SET @CurrentRecoveryModel = NULL

SET @CurrentIsDatabaseAccessible = NULL
SET @CurrentReplicaID = NULL
SET @CurrentAvailabilityGroupID = NULL
SET @CurrentAvailabilityGroup = NULL
SET @CurrentAvailabilityGroupRole = NULL
SET @CurrentDatabaseMirroringRole = NULL

SET @CurrentCommand = NULL

DELETE FROM @tmpIndexesStatistics

END

—————————————————————————————————-
–// Log completing information //–
—————————————————————————————————-

Logging:
SET @EndMessage = ‘Date and time: ‘ + CONVERT(nvarchar,SYSDATETIME(),120)
RAISERROR(‘%s’,10,1,@EndMessage) WITH NOWAIT

RAISERROR(@EmptyLine,10,1) WITH NOWAIT

IF @ReturnCode 0
BEGIN
RETURN @ReturnCode
END

—————————————————————————————————-

END

GO