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,

@DatabaseOrder nvarchar(max) = NULL,

@DatabasesInParallel nvarchar(max) = 'N',

@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: 2020-01-06 16:38:06 //--

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

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 @CurrentUserAccess nvarchar(max)

DECLARE @CurrentIsReadOnly bit

DECLARE @CurrentDatabaseState nvarchar(max)

DECLARE @CurrentInStandby bit

DECLARE @CurrentRecoveryModel nvarchar(max)

DECLARE @CurrentIsDatabaseAccessible bit

DECLARE @CurrentAvailabilityGroup nvarchar(max)

DECLARE @CurrentAvailabilityGroupRole nvarchar(max)

DECLARE @CurrentDatabaseMirroringRole nvarchar(max)

DECLARE @CurrentCommand01 nvarchar(max)

DECLARE @CurrentCommand02 nvarchar(max)

DECLARE @CurrentCommand03 nvarchar(max)

DECLARE @CurrentCommand04 nvarchar(max)

DECLARE @CurrentCommand05 nvarchar(max)

DECLARE @CurrentCommand06 nvarchar(max)

DECLARE @CurrentCommand07 nvarchar(max)

DECLARE @CurrentCommandOutput06 int

DECLARE @CurrentCommandOutput07 int

DECLARE @CurrentCommandType06 nvarchar(max)

DECLARE @CurrentCommandType07 nvarchar(max)

DECLARE @CurrentComment06 nvarchar(max)

DECLARE @CurrentComment07 nvarchar(max)

DECLARE @CurrentExtendedInfo06 xml

DECLARE @CurrentExtendedInfo07 xml

DECLARE @Errors TABLE (ID int IDENTITY PRIMARY KEY,

[Message] nvarchar(max))

DECLARE @CurrentMessage nvarchar(max)

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 datetime2

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 += ', @DatabaseOrder = ' + ISNULL('''' + REPLACE(@DatabaseOrder,'''','''''') + '''','NULL')

SET @Parameters += ', @DatabasesInParallel = ' + ISNULL('''' + REPLACE(@DatabasesInParallel,'''','''''') + '''','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])

SELECT 'The database ' + QUOTENAME(DB_NAME(DB_ID())) + ' has to be in compatibility level 90 or higher.'

END

IF NOT (SELECT uses_ansi_nulls FROM sys.sql_modules WHERE [object_id] = @@PROCID) = 1

BEGIN

INSERT INTO @Errors ([Message])

SELECT 'ANSI_NULLS has to be set to ON for the stored procedure.'

END

IF NOT (SELECT uses_quoted_identifier FROM sys.sql_modules WHERE [object_id] = @@PROCID) = 1

BEGIN

INSERT INTO @Errors ([Message])

SELECT 'QUOTED_IDENTIFIER has to be set to ON for the stored procedure.'

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])

SELECT 'The stored procedure CommandExecute is missing. Download https://ola.hallengren.com/scripts/CommandExecute.sql.'

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])

SELECT 'The stored procedure CommandExecute needs to be updated. Download https://ola.hallengren.com/scripts/CommandExecute.sql.'

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])

SELECT 'The table CommandLog is missing. Download https://ola.hallengren.com/scripts/CommandLog.sql.'

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])

SELECT 'The table Queue is missing. Download https://ola.hallengren.com/scripts/Queue.sql.'

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])

SELECT 'The table QueueDatabase is missing. Download https://ola.hallengren.com/scripts/QueueDatabase.sql.'

END

IF @@TRANCOUNT <> 0

BEGIN

INSERT INTO @Errors ([Message])

SELECT 'The transaction count is not 0.'

END

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

--// Select databases //--

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

SET @Databases = REPLACE(@Databases, CHAR(10), '')

SET @Databases = REPLACE(@Databases, CHAR(13), '')

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

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

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

WITH Databases1 (StartPosition, EndPosition, DatabaseItem) AS

(

SELECT 1 AS StartPosition,

ISNULL(NULLIF(CHARINDEX(',', @Databases, 1), 0), LEN(@Databases) + 1) AS EndPosition,

SUBSTRING(@Databases, 1, ISNULL(NULLIF(CHARINDEX(',', @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(',', @Databases, EndPosition + 1), 0), LEN(@Databases) + 1) AS EndPosition,

SUBSTRING(@Databases, EndPosition + 1, ISNULL(NULLIF(CHARINDEX(',', @Databases, EndPosition + 1), 0), LEN(@Databases) + 1) - EndPosition - 1) AS DatabaseItem

FROM Databases1

WHERE EndPosition < LEN(@Databases) + 1

),

Databases2 (DatabaseItem, StartPosition, Selected) AS

(

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

StartPosition,

CASE WHEN DatabaseItem LIKE '-%' THEN 0 ELSE 1 END AS Selected

FROM Databases1

),

Databases3 (DatabaseItem, DatabaseType, AvailabilityGroup, StartPosition, Selected) AS

(

SELECT CASE WHEN DatabaseItem IN('ALL_DATABASES','SYSTEM_DATABASES','USER_DATABASES','AVAILABILITY_GROUP_DATABASES') THEN '%' ELSE DatabaseItem END AS DatabaseItem,

CASE WHEN DatabaseItem = 'SYSTEM_DATABASES' THEN 'S' WHEN DatabaseItem = 'USER_DATABASES' THEN 'U' ELSE NULL END AS DatabaseType,

CASE WHEN DatabaseItem = 'AVAILABILITY_GROUP_DATABASES' THEN 1 ELSE NULL END AvailabilityGroup,

StartPosition,

Selected

FROM Databases2

),

Databases4 (DatabaseName, DatabaseType, AvailabilityGroup, StartPosition, Selected) AS

(

SELECT CASE WHEN LEFT(DatabaseItem,1) = '[' AND RIGHT(DatabaseItem,1) = ']' THEN PARSENAME(DatabaseItem,1) ELSE DatabaseItem END AS DatabaseItem,

DatabaseType,

AvailabilityGroup,

StartPosition,

Selected

FROM Databases3

)

INSERT INTO @SelectedDatabases (DatabaseName, DatabaseType, AvailabilityGroup, StartPosition, Selected)

SELECT DatabaseName,

DatabaseType,

AvailabilityGroup,

StartPosition,

Selected

FROM Databases4

OPTION (MAXRECURSION 0)

IF @Version >= 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.dm_hadr_availability_replica_states dm_hadr_availability_replica_states ON databases.replica_id = dm_hadr_availability_replica_states.replica_id

INNER JOIN sys.availability_groups availability_groups ON dm_hadr_availability_replica_states.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])

SELECT 'The value for the parameter @Databases is not supported.'

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(', ',@AvailabilityGroups) > 0 SET @AvailabilityGroups = REPLACE(@AvailabilityGroups,', ',',')

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

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

WITH AvailabilityGroups1 (StartPosition, EndPosition, AvailabilityGroupItem) AS

(

SELECT 1 AS StartPosition,

ISNULL(NULLIF(CHARINDEX(',', @AvailabilityGroups, 1), 0), LEN(@AvailabilityGroups) + 1) AS EndPosition,

SUBSTRING(@AvailabilityGroups, 1, ISNULL(NULLIF(CHARINDEX(',', @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(',', @AvailabilityGroups, EndPosition + 1), 0), LEN(@AvailabilityGroups) + 1) AS EndPosition,

SUBSTRING(@AvailabilityGroups, EndPosition + 1, ISNULL(NULLIF(CHARINDEX(',', @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 < 11 OR SERVERPROPERTY('IsHadrEnabled') = 0)

BEGIN

INSERT INTO @Errors ([Message])

SELECT 'The value for the parameter @AvailabilityGroups is not supported.'

END

IF (@Databases IS NULL AND @AvailabilityGroups IS NULL)

BEGIN

INSERT INTO @Errors ([Message])

SELECT 'You need to specify one of the parameters @Databases and @AvailabilityGroups.'

END

IF (@Databases IS NOT NULL AND @AvailabilityGroups IS NOT NULL)

BEGIN

INSERT INTO @Errors ([Message])

SELECT 'You can only specify one of the parameters @Databases and @AvailabilityGroups.'

END

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

--// Select indexes //--

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

SET @Indexes = REPLACE(@Indexes, CHAR(10), '')

SET @Indexes = REPLACE(@Indexes, CHAR(13), '')

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

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

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

WITH Indexes1 (StartPosition, EndPosition, IndexItem) AS

(

SELECT 1 AS StartPosition,

ISNULL(NULLIF(CHARINDEX(',', @Indexes, 1), 0), LEN(@Indexes) + 1) AS EndPosition,

SUBSTRING(@Indexes, 1, ISNULL(NULLIF(CHARINDEX(',', @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(',', @Indexes, EndPosition + 1), 0), LEN(@Indexes) + 1) AS EndPosition,

SUBSTRING(@Indexes, EndPosition + 1, ISNULL(NULLIF(CHARINDEX(',', @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,', ',',');

WITH FragmentationLow (StartPosition, EndPosition, [Action]) AS

(

SELECT 1 AS StartPosition,

ISNULL(NULLIF(CHARINDEX(',', @FragmentationLow, 1), 0), LEN(@FragmentationLow) + 1) AS EndPosition,

SUBSTRING(@FragmentationLow, 1, ISNULL(NULLIF(CHARINDEX(',', @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(',', @FragmentationLow, EndPosition + 1), 0), LEN(@FragmentationLow) + 1) AS EndPosition,

SUBSTRING(@FragmentationLow, EndPosition + 1, ISNULL(NULLIF(CHARINDEX(',', @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,', ',',');

WITH FragmentationMedium (StartPosition, EndPosition, [Action]) AS

(

SELECT 1 AS StartPosition,

ISNULL(NULLIF(CHARINDEX(',', @FragmentationMedium, 1), 0), LEN(@FragmentationMedium) + 1) AS EndPosition,

SUBSTRING(@FragmentationMedium, 1, ISNULL(NULLIF(CHARINDEX(',', @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(',', @FragmentationMedium, EndPosition + 1), 0), LEN(@FragmentationMedium) + 1) AS EndPosition,

SUBSTRING(@FragmentationMedium, EndPosition + 1, ISNULL(NULLIF(CHARINDEX(',', @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,', ',',');

WITH FragmentationHigh (StartPosition, EndPosition, [Action]) AS

(

SELECT 1 AS StartPosition,

ISNULL(NULLIF(CHARINDEX(',', @FragmentationHigh, 1), 0), LEN(@FragmentationHigh) + 1) AS EndPosition,

SUBSTRING(@FragmentationHigh, 1, ISNULL(NULLIF(CHARINDEX(',', @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(',', @FragmentationHigh, EndPosition + 1), 0), LEN(@FragmentationHigh) + 1) AS EndPosition,

SUBSTRING(@FragmentationHigh, EndPosition + 1, ISNULL(NULLIF(CHARINDEX(',', @FragmentationHigh, EndPosition + 1), 0), LEN(@FragmentationHigh) + 1) - EndPosition - 1) AS [Action]

FROM FragmentationHigh

WHERE EndPosition < LEN(@FragmentationHigh) + 1

)

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

SELECT 'High' AS FragmentationGroup,

ROW_NUMBER() OVER(ORDER BY StartPosition ASC) AS [Priority],

[Action]

FROM FragmentationHigh

OPTION (MAXRECURSION 0)

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

--// Check input parameters //--

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

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

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

BEGIN

INSERT INTO @Errors ([Message])

SELECT 'The value for the parameter @FragmentationLow is not supported.'

END

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

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

BEGIN

INSERT INTO @Errors ([Message])

SELECT 'The value for the parameter @FragmentationMedium is not supported.'

END

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

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

BEGIN

INSERT INTO @Errors ([Message])

SELECT 'The value for the parameter @FragmentationHigh is not supported.'

END

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

BEGIN

INSERT INTO @Errors ([Message])

SELECT 'The value for the parameter @FragmentationLevel1 is not supported.'

END

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

BEGIN

INSERT INTO @Errors ([Message])

SELECT 'The value for the parameter @FragmentationLevel2 is not supported.'

END

IF @MinNumberOfPages < 0 OR @MinNumberOfPages IS NULL

BEGIN

INSERT INTO @Errors ([Message])

SELECT 'The value for the parameter @MinNumberOfPages is not supported.'

END

IF @MaxNumberOfPages < 0

BEGIN

INSERT INTO @Errors ([Message])

SELECT 'The value for the parameter @MaxNumberOfPages is not supported.'

END

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

BEGIN

INSERT INTO @Errors ([Message])

SELECT 'The value for the parameter @SortInTempdb is not supported.'

END

IF @MaxDOP < 0 OR @MaxDOP > 64

BEGIN

INSERT INTO @Errors ([Message])

SELECT 'The value for the parameter @MaxDOP is not supported.'

END

IF @FillFactor <= 0 OR @FillFactor > 100

BEGIN

INSERT INTO @Errors ([Message])

SELECT 'The value for the parameter @FillFactor is not supported.'

END

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

BEGIN

INSERT INTO @Errors ([Message])

SELECT 'The value for the parameter @PadIndex is not supported.'

END

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

BEGIN

INSERT INTO @Errors ([Message])

SELECT 'The value for the parameter @LOBCompaction is not supported.'

END

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

BEGIN

INSERT INTO @Errors ([Message])

SELECT 'The value for the parameter @UpdateStatistics is not supported.'

END

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

BEGIN

INSERT INTO @Errors ([Message])

SELECT 'The value for the parameter @OnlyModifiedStatistics is not supported.'

END

IF @StatisticsModificationLevel <= 0 OR @StatisticsModificationLevel > 100

BEGIN

INSERT INTO @Errors ([Message])

SELECT 'The value for the parameter @StatisticsModificationLevel is not supported.'

END

IF @OnlyModifiedStatistics = 'Y' AND @StatisticsModificationLevel IS NOT NULL

BEGIN

INSERT INTO @Errors ([Message])

SELECT 'You can only specify one of the parameters @OnlyModifiedStatistics and @StatisticsModificationLevel.'

END

IF @StatisticsSample <= 0 OR @StatisticsSample > 100

BEGIN

INSERT INTO @Errors ([Message])

SELECT 'The value for the parameter @StatisticsSample is not supported.'

END

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

BEGIN

INSERT INTO @Errors ([Message])

SELECT 'The value for the parameter @StatisticsResample is not supported.'

END

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

BEGIN

INSERT INTO @Errors ([Message])

SELECT 'The value for the parameter @PartitionLevel is not supported.'

END

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

BEGIN

INSERT INTO @Errors ([Message])

SELECT 'The value for the parameter @MSShippedObjects is not supported.'

END

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

BEGIN

INSERT INTO @Errors ([Message])

SELECT 'The value for the parameter @Indexes is not supported.'

END

IF @TimeLimit < 0

BEGIN

INSERT INTO @Errors ([Message])

SELECT 'The value for the parameter @TimeLimit is not supported.'

END

IF @Delay < 0

BEGIN

INSERT INTO @Errors ([Message])

SELECT 'The value for the parameter @Delay is not supported.'

END

IF @WaitAtLowPriorityMaxDuration < 0 OR (@WaitAtLowPriorityMaxDuration IS NOT NULL AND @Version < 12) OR (@WaitAtLowPriorityMaxDuration IS NOT NULL AND @WaitAtLowPriorityAbortAfterWait IS NULL) OR (@WaitAtLowPriorityMaxDuration IS NULL AND @WaitAtLowPriorityAbortAfterWait IS NOT NULL)

BEGIN

INSERT INTO @Errors ([Message])

SELECT 'The value for the parameter @WaitAtLowPriorityMaxDuration is not supported.'

END

IF @WaitAtLowPriorityAbortAfterWait NOT IN('NONE','SELF','BLOCKERS') OR (@WaitAtLowPriorityAbortAfterWait IS NOT NULL AND @Version < 12) OR (@WaitAtLowPriorityAbortAfterWait IS NOT NULL AND @WaitAtLowPriorityMaxDuration IS NULL) OR (@WaitAtLowPriorityAbortAfterWait IS NULL AND @WaitAtLowPriorityMaxDuration IS NOT NULL)

BEGIN

INSERT INTO @Errors ([Message])

SELECT 'The value for the parameter @WaitAtLowPriorityAbortAfterWait is not supported.'

END

IF @Resumable NOT IN('Y','N') OR @Resumable IS NULL OR (@Resumable = 'Y' AND NOT (@Version >= 14 OR SERVERPROPERTY('EngineEdition') IN (5,8)))

BEGIN

INSERT INTO @Errors ([Message])

SELECT 'The value for the parameter @Resumable is not supported.'

END

IF @Resumable = 'Y' AND @SortInTempdb = 'Y'

BEGIN

INSERT INTO @Errors ([Message])

SELECT 'You can only specify one of the parameters @Resumable and @SortInTempdb.'

END

IF @LockTimeout < 0

BEGIN

INSERT INTO @Errors ([Message])

SELECT 'The value for the parameter @LockTimeout is not supported.'

END

IF @LockMessageSeverity NOT IN(10,16)

BEGIN

INSERT INTO @Errors ([Message])

SELECT 'The value for the parameter @LockMessageSeverity is not supported.'

END

IF @DatabaseOrder NOT IN('DATABASE_NAME_ASC','DATABASE_NAME_DESC','DATABASE_SIZE_ASC','DATABASE_SIZE_DESC') OR (@DatabaseOrder IS NOT NULL AND SERVERPROPERTY('EngineEdition') = 5)

BEGIN

INSERT INTO @Errors ([Message])

SELECT 'The value for the parameter @DatabaseOrder is not supported.'

END

IF @DatabasesInParallel NOT IN('Y','N') OR @DatabasesInParallel IS NULL OR (@DatabasesInParallel = 'Y' AND SERVERPROPERTY('EngineEdition') = 5)

BEGIN

INSERT INTO @Errors ([Message])

SELECT 'The value for the parameter @DatabasesInParallel is not supported.'

END

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

BEGIN

INSERT INTO @Errors ([Message])

SELECT 'The value for the parameter @LogToTable is not supported.'

END

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

BEGIN

INSERT INTO @Errors ([Message])

SELECT 'The value for the parameter @Execute is not supported.'

END

IF EXISTS(SELECT * FROM @Errors)

BEGIN

INSERT INTO @Errors ([Message])

SELECT 'The documentation is available at https://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html.'

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])

SELECT 'The following databases in the @Databases parameter do not exist: ' + LEFT(@ErrorMessage,LEN(@ErrorMessage)-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])

SELECT 'The following databases in the @Indexes parameter do not exist: ' + LEFT(@ErrorMessage,LEN(@ErrorMessage)-1) + '.'

END

SET @ErrorMessage = ''