SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[DatabaseIntegrityCheck]') AND type in (N'P', N'PC'))

BEGIN

EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[DatabaseIntegrityCheck] AS'

END

GO

ALTER PROCEDURE [dbo].[DatabaseIntegrityCheck]

@Databases nvarchar(max) = NULL,

@CheckCommands nvarchar(max) = 'CHECKDB',

@PhysicalOnly nvarchar(max) = 'N',

@NoIndex nvarchar(max) = 'N',

@ExtendedLogicalChecks nvarchar(max) = 'N',

@TabLock nvarchar(max) = 'N',

@FileGroups nvarchar(max) = NULL,

@Objects nvarchar(max) = NULL,

@MaxDOP int = NULL,

@AvailabilityGroups nvarchar(max) = NULL,

@AvailabilityGroupReplicas nvarchar(max) = 'ALL',

@Updateability nvarchar(max) = 'ALL',

@TimeLimit int = 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: 2019-01-11 21:26:19 //--

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

SET NOCOUNT ON

DECLARE @StartMessage nvarchar(max)

DECLARE @EndMessage nvarchar(max)

DECLARE @DatabaseMessage nvarchar(max)

DECLARE @ErrorMessage nvarchar(max)

DECLARE @Severity int

DECLARE @StartTime datetime

DECLARE @SchemaName nvarchar(max)

DECLARE @ObjectName nvarchar(max)

DECLARE @VersionTimestamp nvarchar(max)

DECLARE @Parameters nvarchar(max)

DECLARE @Version numeric(18,10)

DECLARE @HostPlatform nvarchar(max)

DECLARE @AmazonRDS bit

DECLARE @QueueID int

DECLARE @QueueStartTime datetime

DECLARE @CurrentDBID int

DECLARE @CurrentDatabaseID int

DECLARE @CurrentDatabaseName nvarchar(max)

DECLARE @CurrentIsDatabaseAccessible bit

DECLARE @CurrentAvailabilityGroup nvarchar(max)

DECLARE @CurrentAvailabilityGroupRole nvarchar(max)

DECLARE @CurrentAvailabilityGroupBackupPreference nvarchar(max)

DECLARE @CurrentIsPreferredBackupReplica bit

DECLARE @CurrentDatabaseMirroringRole nvarchar(max)

DECLARE @CurrentIsReadOnly bit

DECLARE @CurrentFGID int

DECLARE @CurrentFileGroupID int

DECLARE @CurrentFileGroupName nvarchar(max)

DECLARE @CurrentFileGroupExists bit

DECLARE @CurrentOID int

DECLARE @CurrentSchemaID int

DECLARE @CurrentSchemaName nvarchar(max)

DECLARE @CurrentObjectID int

DECLARE @CurrentObjectName nvarchar(max)

DECLARE @CurrentObjectType nvarchar(max)

DECLARE @CurrentObjectExists bit

DECLARE @CurrentCommand01 nvarchar(max)

DECLARE @CurrentCommand02 nvarchar(max)

DECLARE @CurrentCommand03 nvarchar(max)

DECLARE @CurrentCommand04 nvarchar(max)

DECLARE @CurrentCommand05 nvarchar(max)

DECLARE @CurrentCommand06 nvarchar(max)

DECLARE @CurrentCommand07 nvarchar(max)

DECLARE @CurrentCommand08 nvarchar(max)

DECLARE @CurrentCommand09 nvarchar(max)

DECLARE @CurrentCommandOutput01 int

DECLARE @CurrentCommandOutput04 int

DECLARE @CurrentCommandOutput05 int

DECLARE @CurrentCommandOutput08 int

DECLARE @CurrentCommandOutput09 int

DECLARE @CurrentCommandType01 nvarchar(max)

DECLARE @CurrentCommandType04 nvarchar(max)

DECLARE @CurrentCommandType05 nvarchar(max)

DECLARE @CurrentCommandType08 nvarchar(max)

DECLARE @CurrentCommandType09 nvarchar(max)

DECLARE @tmpDatabases TABLE (ID int IDENTITY,

DatabaseName nvarchar(max),

DatabaseType nvarchar(max),

AvailabilityGroup bit,

[Snapshot] bit,

StartPosition int,

LastCommandTime datetime,

DatabaseSize bigint,

LastGoodCheckDbTime datetime,

[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 @tmpFileGroups TABLE (ID int IDENTITY,

FileGroupID int,

FileGroupName nvarchar(max),

StartPosition int,

[Order] int,

Selected bit,

Completed bit,

PRIMARY KEY(Selected, Completed, [Order], ID))

DECLARE @tmpObjects TABLE (ID int IDENTITY,

SchemaID int,

SchemaName nvarchar(max),

ObjectID int,

ObjectName nvarchar(max),

ObjectType nvarchar(max),

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 @SelectedFileGroups TABLE (DatabaseName nvarchar(max),

FileGroupName nvarchar(max),

StartPosition int,

Selected bit)

DECLARE @SelectedObjects TABLE (DatabaseName nvarchar(max),

SchemaName nvarchar(max),

ObjectName nvarchar(max),

StartPosition int,

Selected bit)

DECLARE @SelectedCheckCommands TABLE (CheckCommand nvarchar(max))

DECLARE @Error int

DECLARE @ReturnCode int

SET @Error = 0

SET @ReturnCode = 0

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

IF @Version >= 14

BEGIN

SELECT @HostPlatform = host_platform

FROM sys.dm_os_host_info

END

ELSE

BEGIN

SET @HostPlatform = 'Windows'

END

SET @AmazonRDS = CASE WHEN DB_ID('rdsadmin') IS NOT NULL AND SUSER_SNAME(0x01) = 'rdsa' THEN 1 ELSE 0 END

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

--// Log initial information //--

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

SET @StartTime = GETDATE()

SET @SchemaName = (SELECT schemas.name FROM sys.schemas schemas INNER JOIN sys.objects objects ON schemas.[schema_id] = objects.[schema_id] WHERE [object_id] = @@PROCID)

SET @ObjectName = OBJECT_NAME(@@PROCID)

SET @VersionTimestamp = SUBSTRING(OBJECT_DEFINITION(@@PROCID),CHARINDEX('--// Version: ',OBJECT_DEFINITION(@@PROCID)) + LEN('--// Version: ') + 1, 19)

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

SET @Parameters = @Parameters + ', @CheckCommands = ' + ISNULL('''' + REPLACE(@CheckCommands,'''','''''') + '''','NULL')

SET @Parameters = @Parameters + ', @PhysicalOnly = ' + ISNULL('''' + REPLACE(@PhysicalOnly,'''','''''') + '''','NULL')

SET @Parameters = @Parameters + ', @NoIndex = ' + ISNULL('''' + REPLACE(@NoIndex,'''','''''') + '''','NULL')

SET @Parameters = @Parameters + ', @ExtendedLogicalChecks = ' + ISNULL('''' + REPLACE(@ExtendedLogicalChecks,'''','''''') + '''','NULL')

SET @Parameters = @Parameters + ', @TabLock = ' + ISNULL('''' + REPLACE(@TabLock,'''','''''') + '''','NULL')

SET @Parameters = @Parameters + ', @FileGroups = ' + ISNULL('''' + REPLACE(@FileGroups,'''','''''') + '''','NULL')

SET @Parameters = @Parameters + ', @Objects = ' + ISNULL('''' + REPLACE(@Objects,'''','''''') + '''','NULL')

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

SET @Parameters = @Parameters + ', @AvailabilityGroups = ' + ISNULL('''' + REPLACE(@AvailabilityGroups,'''','''''') + '''','NULL')

SET @Parameters = @Parameters + ', @AvailabilityGroupReplicas = ' + ISNULL('''' + REPLACE(@AvailabilityGroupReplicas,'''','''''') + '''','NULL')

SET @Parameters = @Parameters + ', @Updateability = ' + ISNULL('''' + REPLACE(@Updateability,'''','''''') + '''','NULL')

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

SET @Parameters = @Parameters + ', @LockTimeout = ' + ISNULL(CAST(@LockTimeout AS nvarchar),'NULL')

SET @Parameters = @Parameters + ', @LockMessageSeverity = ' + ISNULL(CAST(@LockMessageSeverity AS nvarchar),'NULL')

SET @Parameters = @Parameters + ', @DatabaseOrder = ' + ISNULL('''' + REPLACE(@DatabaseOrder,'''','''''') + '''','NULL')

SET @Parameters = @Parameters + ', @DatabasesInParallel = ' + ISNULL('''' + REPLACE(@DatabasesInParallel,'''','''''') + '''','NULL')

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

SET @Parameters = @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' + CHAR(13) + CHAR(10) + ' '

RAISERROR('%s',10,1,@StartMessage) WITH NOWAIT

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

--// Check core requirements //--

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

IF NOT (SELECT [compatibility_level] FROM sys.databases WHERE database_id = DB_ID()) >= 90

BEGIN

SET @ErrorMessage = 'The database ' + QUOTENAME(DB_NAME(DB_ID())) + ' has to be in compatibility level 90 or higher.' + CHAR(13) + CHAR(10) + ' '

RAISERROR('%s',16,1,@ErrorMessage) WITH NOWAIT

SET @Error = @@ERROR

END

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

BEGIN

SET @ErrorMessage = 'ANSI_NULLS has to be set to ON for the stored procedure.' + CHAR(13) + CHAR(10) + ' '

RAISERROR('%s',16,1,@ErrorMessage) WITH NOWAIT

SET @Error = @@ERROR

END

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

BEGIN

SET @ErrorMessage = 'QUOTED_IDENTIFIER has to be set to ON for the stored procedure.' + CHAR(13) + CHAR(10) + ' '

RAISERROR('%s',16,1,@ErrorMessage) WITH NOWAIT

SET @Error = @@ERROR

END

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

BEGIN

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

RAISERROR('%s',16,1,@ErrorMessage) WITH NOWAIT

SET @Error = @@ERROR

END

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

BEGIN

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

RAISERROR('%s',16,1,@ErrorMessage) WITH NOWAIT

SET @Error = @@ERROR

END

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

BEGIN

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

RAISERROR('%s',16,1,@ErrorMessage) WITH NOWAIT

SET @Error = @@ERROR

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

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

RAISERROR('%s',16,1,@ErrorMessage) WITH NOWAIT

SET @Error = @@ERROR

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

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

RAISERROR('%s',16,1,@ErrorMessage) WITH NOWAIT

SET @Error = @@ERROR

END

IF @@TRANCOUNT <> 0

BEGIN

SET @ErrorMessage = 'The transaction count is not 0.' + CHAR(13) + CHAR(10) + ' '

RAISERROR('%s',16,1,@ErrorMessage) WITH NOWAIT

SET @Error = @@ERROR

END

IF @Error <> 0

BEGIN

SET @ReturnCode = @Error

GOTO Logging

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 availability_databases_cluster.database_name, availability_groups.name

FROM sys.availability_databases_cluster availability_databases_cluster

INNER JOIN sys.availability_groups availability_groups ON availability_databases_cluster.group_id = availability_groups.group_id

END

INSERT INTO @tmpDatabases (DatabaseName, DatabaseType, AvailabilityGroup, [Snapshot], [Order], Selected, Completed)

SELECT [name] AS DatabaseName,

CASE WHEN name IN('master','msdb','model') THEN 'S' ELSE 'U' END AS DatabaseType,

NULL AS AvailabilityGroup,

CASE WHEN source_database_id IS NOT NULL THEN 1 ELSE 0 END AS [Snapshot],

0 AS [Order],

0 AS Selected,

0 AS Completed

FROM sys.databases

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)

AND NOT ((tmpDatabases.DatabaseName = 'tempdb' OR tmpDatabases.[Snapshot] = 1) AND tmpDatabases.DatabaseName <> SelectedDatabases.DatabaseName)

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)

AND NOT ((tmpDatabases.DatabaseName = 'tempdb' OR tmpDatabases.[Snapshot] = 1) AND tmpDatabases.DatabaseName <> SelectedDatabases.DatabaseName)

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

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

RAISERROR('%s',16,1,@ErrorMessage) WITH NOWAIT

SET @Error = @@ERROR

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

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

RAISERROR('%s',16,1,@ErrorMessage) WITH NOWAIT

SET @Error = @@ERROR

END

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

BEGIN

SET @ErrorMessage = 'You need to specify one of the parameters @Databases and @AvailabilityGroups.' + CHAR(13) + CHAR(10) + ' '

RAISERROR('%s',16,1,@ErrorMessage) WITH NOWAIT

SET @Error = @@ERROR

END

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

BEGIN

SET @ErrorMessage = 'You can only specify one of the parameters @Databases and @AvailabilityGroups.' + CHAR(13) + CHAR(10) + ' '

RAISERROR('%s',16,1,@ErrorMessage) WITH NOWAIT

SET @Error = @@ERROR

END

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

--// Select filegroups //--

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

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

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

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

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

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

WITH FileGroups1 (StartPosition, EndPosition, FileGroupItem) AS

(

SELECT 1 AS StartPosition,

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

SUBSTRING(@FileGroups, 1, ISNULL(NULLIF(CHARINDEX(',', @FileGroups, 1), 0), LEN(@FileGroups) + 1) - 1) AS FileGroupItem

WHERE @FileGroups IS NOT NULL

UNION ALL

SELECT CAST(EndPosition AS int) + 1 AS StartPosition,

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

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

FROM FileGroups1

WHERE EndPosition < LEN(@FileGroups) + 1

),

FileGroups2 (FileGroupItem, StartPosition, Selected) AS

(

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

StartPosition,

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

FROM FileGroups1

),

FileGroups3 (FileGroupItem, StartPosition, Selected) AS

(

SELECT CASE WHEN FileGroupItem = 'ALL_FILEGROUPS' THEN '%.%' ELSE FileGroupItem END AS FileGroupItem,

StartPosition,

Selected

FROM FileGroups2

),

FileGroups4 (DatabaseName, FileGroupName, StartPosition, Selected) AS

(

SELECT CASE WHEN PARSENAME(FileGroupItem,4) IS NULL AND PARSENAME(FileGroupItem,3) IS NULL THEN PARSENAME(FileGroupItem,2) ELSE NULL END AS DatabaseName,

CASE WHEN PARSENAME(FileGroupItem,4) IS NULL AND PARSENAME(FileGroupItem,3) IS NULL THEN PARSENAME(FileGroupItem,1) ELSE NULL END AS FileGroupName,

StartPosition,

Selected

FROM FileGroups3

)

INSERT INTO @SelectedFileGroups (DatabaseName, FileGroupName, StartPosition, Selected)

SELECT DatabaseName, FileGroupName, StartPosition, Selected

FROM FileGroups4

OPTION (MAXRECURSION 0)

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

--// Select objects //--

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

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

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

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

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

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

WITH Objects1 (StartPosition, EndPosition, ObjectItem) AS

(

SELECT 1 AS StartPosition,

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

SUBSTRING(@Objects, 1, ISNULL(NULLIF(CHARINDEX(',', @Objects, 1), 0), LEN(@Objects) + 1) - 1) AS ObjectItem

WHERE @Objects IS NOT NULL

UNION ALL

SELECT CAST(EndPosition AS int) + 1 AS StartPosition,

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

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

FROM Objects1

WHERE EndPosition < LEN(@Objects) + 1

),

Objects2 (ObjectItem, StartPosition, Selected) AS

(

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

StartPosition,

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

FROM Objects1

),

Objects3 (ObjectItem, StartPosition, Selected) AS

(

SELECT CASE WHEN ObjectItem = 'ALL_OBJECTS' THEN '%.%.%' ELSE ObjectItem END AS ObjectItem,

StartPosition,

Selected

FROM Objects2

),

Objects4 (DatabaseName, SchemaName, ObjectName, StartPosition, Selected) AS

(

SELECT CASE WHEN PARSENAME(ObjectItem,4) IS NULL THEN PARSENAME(ObjectItem,3) ELSE NULL END AS DatabaseName,

CASE WHEN PARSENAME(ObjectItem,4) IS NULL THEN PARSENAME(ObjectItem,2) ELSE NULL END AS SchemaName,

CASE WHEN PARSENAME(ObjectItem,4) IS NULL THEN PARSENAME(ObjectItem,1) ELSE NULL END AS ObjectName,

StartPosition,

Selected

FROM Objects3

)

INSERT INTO @SelectedObjects (DatabaseName, SchemaName, ObjectName, StartPosition, Selected)

SELECT DatabaseName, SchemaName, ObjectName, StartPosition, Selected

FROM Objects4

OPTION (MAXRECURSION 0);

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

--// Select check commands //--

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

WITH CheckCommands (StartPosition, EndPosition, CheckCommand) AS

(

SELECT 1 AS StartPosition,

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

SUBSTRING(@CheckCommands, 1, ISNULL(NULLIF(CHARINDEX(',', @CheckCommands, 1), 0), LEN(@CheckCommands) + 1) - 1) AS CheckCommand

WHERE @CheckCommands IS NOT NULL

UNION ALL

SELECT CAST(EndPosition AS int) + 1 AS StartPosition,

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

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

FROM CheckCommands

WHERE EndPosition < LEN(@CheckCommands) + 1

)

INSERT INTO @SelectedCheckCommands (CheckCommand)

SELECT CheckCommand

FROM CheckCommands

OPTION (MAXRECURSION 0)

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

--// Check input parameters //--

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

IF EXISTS (SELECT * FROM @SelectedCheckCommands WHERE CheckCommand NOT IN('CHECKDB','CHECKFILEGROUP','CHECKALLOC','CHECKTABLE','CHECKCATALOG')) OR EXISTS (SELECT * FROM @SelectedCheckCommands GROUP BY CheckCommand HAVING COUNT(*) > 1) OR NOT EXISTS (SELECT * FROM @SelectedCheckCommands) OR (EXISTS (SELECT * FROM @SelectedCheckCommands WHERE CheckCommand IN('CHECKDB')) AND EXISTS (SELECT CheckCommand FROM @SelectedCheckCommands WHERE CheckCommand IN('CHECKFILEGROUP','CHECKALLOC','CHECKTABLE','CHECKCATALOG'))) OR (EXISTS (SELECT * FROM @SelectedCheckCommands WHERE CheckCommand IN('CHECKFILEGROUP')) AND EXISTS (SELECT CheckCommand FROM @SelectedCheckCommands WHERE CheckCommand IN('CHECKALLOC','CHECKTABLE')))

BEGIN

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

RAISERROR('%s',16,1,@ErrorMessage) WITH NOWAIT

SET @Error = @@ERROR

END

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

BEGIN

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

RAISERROR('%s',16,1,@ErrorMessage) WITH NOWAIT

SET @Error = @@ERROR

END

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

BEGIN

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

RAISERROR('%s',16,1,@ErrorMessage) WITH NOWAIT

SET @Error = @@ERROR

END

IF @ExtendedLogicalChecks NOT IN ('Y','N') OR @ExtendedLogicalChecks IS NULL OR (@ExtendedLogicalChecks = 'Y' AND NOT @Version >= 10) OR (@PhysicalOnly = 'Y' AND @ExtendedLogicalChecks = 'Y')

BEGIN

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

RAISERROR('%s',16,1,@ErrorMessage) WITH NOWAIT

SET @Error = @@ERROR

END

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

BEGIN

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

RAISERROR('%s',16,1,@ErrorMessage) WITH NOWAIT

SET @Error = @@ERROR

END

IF EXISTS(SELECT * FROM @SelectedFileGroups WHERE DatabaseName IS NULL OR FileGroupName IS NULL) OR (@FileGroups IS NOT NULL AND NOT EXISTS(SELECT * FROM @SelectedFileGroups)) OR (@FileGroups IS NOT NULL AND NOT EXISTS (SELECT * FROM @SelectedCheckCommands WHERE CheckCommand = 'CHECKFILEGROUP'))

BEGIN

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

RAISERROR('%s',16,1,@ErrorMessage) WITH NOWAIT

SET @Error = @@ERROR

END

IF EXISTS(SELECT * FROM @SelectedObjects WHERE DatabaseName IS NULL OR SchemaName IS NULL OR ObjectName IS NULL) OR (@Objects IS NOT NULL AND NOT EXISTS(SELECT * FROM @SelectedObjects)) OR (@Objects IS NOT NULL AND NOT EXISTS (SELECT * FROM @SelectedCheckCommands WHERE CheckCommand = 'CHECKTABLE'))

BEGIN

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

RAISERROR('%s',16,1,@ErrorMessage) WITH NOWAIT

SET @Error = @@ERROR

END

IF @MaxDOP < 0 OR @MaxDOP > 64 OR (@MaxDOP IS NOT NULL AND NOT (@Version >= 12.050000 OR SERVERPROPERTY('EngineEdition') IN (5, 8)))

BEGIN

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

RAISERROR('%s',16,1,@ErrorMessage) WITH NOWAIT

SET @Error = @@ERROR

END

IF @AvailabilityGroupReplicas NOT IN('ALL','PRIMARY','SECONDARY','PREFERRED_BACKUP_REPLICA') OR @AvailabilityGroupReplicas IS NULL

BEGIN

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

RAISERROR('%s',16,1,@ErrorMessage) WITH NOWAIT

SET @Error = @@ERROR

END

IF @Updateability NOT IN('READ_ONLY','READ_WRITE','ALL') OR @Updateability IS NULL

BEGIN

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

RAISERROR('%s',16,1,@ErrorMessage) WITH NOWAIT

SET @Error = @@ERROR

END

IF @TimeLimit < 0

BEGIN

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

RAISERROR('%s',16,1,@ErrorMessage) WITH NOWAIT

SET @Error = @@ERROR

END

IF @LockTimeout < 0

BEGIN

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

RAISERROR('%s',16,1,@ErrorMessage) WITH NOWAIT

SET @Error = @@ERROR

END

IF @LockMessageSeverity NOT IN(10,16)

BEGIN

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

RAISERROR('%s',16,1,@ErrorMessage) WITH NOWAIT

SET @Error = @@ERROR

END

IF @DatabaseOrder NOT IN('DATABASE_NAME_ASC','DATABASE_NAME_DESC','DATABASE_SIZE_ASC','DATABASE_SIZE_DESC','DATABASE_LAST_GOOD_CHECK_ASC','DATABASE_LAST_GOOD_CHECK_DESC','REPLICA_LAST_GOOD_CHECK_ASC','REPLICA_LAST_GOOD_CHECK_DESC') OR (@DatabaseOrder IN('DATABASE_LAST_GOOD_CHECK_ASC','DATABASE_LAST_GOOD_CHECK_DESC') AND NOT ((@Version >= 12.06024 AND @Version < 13) OR (@Version >= 13.05026 AND @Version < 14) OR @Version >= 14.0302916)) OR (@DatabaseOrder IN('REPLICA_LAST_GOOD_CHECK_ASC','REPLICA_LAST_GOOD_CHECK_DESC') AND @LogToTable = 'N') OR (@DatabaseOrder IN('DATABASE_LAST_GOOD_CHECK_ASC','DATABASE_LAST_GOOD_CHECK_DESC','REPLICA_LAST_GOOD_CHECK_ASC','REPLICA_LAST_GOOD_CHECK_DESC') AND @CheckCommands <> 'CHECKDB') OR (@DatabaseOrder IS NOT NULL AND SERVERPROPERTY('EngineEdition') = 5)

BEGIN

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

RAISERROR('%s',16,1,@ErrorMessage) WITH NOWAIT

SET @Error = @@ERROR

END

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

BEGIN

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

RAISERROR('%s',16,1,@ErrorMessage) WITH NOWAIT

SET @Error = @@ERROR

END

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

BEGIN

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

RAISERROR('%s',16,1,@ErrorMessage) WITH NOWAIT

SET @Error = @@ERROR

END

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

BEGIN

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

RAISERROR('%s',16,1,@ErrorMessage) WITH NOWAIT

SET @Error = @@ERROR

END

IF @Error <> 0

BEGIN

SET @ErrorMessage = 'The documentation is available at https://ola.hallengren.com/sql-server-integrity-check.html.' + CHAR(13) + CHAR(10) + ' '

RAISERROR('%s',16,1,@ErrorMessage) WITH NOWAIT

SET @ReturnCode = @Error

GOTO Logging

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

SET @ErrorMessage = 'The following databases in the @Databases parameter do not exist: ' + LEFT(@ErrorMessage,LEN(@ErrorMessage)-1) + '.' + CHAR(13) + CHAR(10) + ' '

RAISERROR('%s',10,1,@ErrorMessage) WITH NOWAIT

SET @Error = @@ERROR

END

SET @ErrorMessage = ''

SELECT @ErrorMessage = @ErrorMessage + QUOTENAME(DatabaseName) + ', '

FROM @SelectedFileGroups

WHERE DatabaseName NOT LIKE '%[%]%'

AND DatabaseName NOT IN (SELECT DatabaseName FROM @tmpDatabases)

IF @@ROWCOUNT > 0

BEGIN

SET @ErrorMessage = 'The following databases in the @FileGroups parameter do not exist: ' + LEFT(@ErrorMessage,LEN(@ErrorMessage)-1) + '.' + CHAR(13) + CHAR(10) + ' '

RAISERROR('%s',10,1,@ErrorMessage) WITH NOWAIT

SET @Error = @@ERROR

END

SET @ErrorMessage = ''

SELECT @ErrorMessage = @ErrorMessage + QUOTENAME(DatabaseName) + ', '

FROM @SelectedObjects

WHERE DatabaseName NOT LIKE '%[%]%'

AND DatabaseName NOT IN (SELECT DatabaseName FROM @tmpDatabases)

IF @@ROWCOUNT > 0

BEGIN

SET @ErrorMessage = 'The following databases in the @Objects parameter do not exist: ' + LEFT(@ErrorMessage,LEN(@ErrorMessage)-1) + '.' + CHAR(13) + CHAR(10) + ' '

RAISERROR('%s',10,1,@ErrorMessage) WITH NOWAIT

SET @Error = @@ERROR

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

SET @ErrorMessage = 'The following availability groups do not exist: ' + LEFT(@ErrorMessage,LEN(@ErrorMessage)-1) + '.' + CHAR(13) + CHAR(10) + ' '

RAISERROR('%s',10,1,@ErrorMessage) WITH NOWAIT

SET @Error = @@ERROR

END

SET @ErrorMessage = ''

SELECT @ErrorMessage = @ErrorMessage + QUOTENAME(DatabaseName) + ', '

FROM @SelectedFileGroups

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

SET @ErrorMessage = 'The following databases have been selected in the @FileGroups parameter, but not in the @Databases or @AvailabilityGroups parameters: ' + LEFT(@ErrorMessage,LEN(@ErrorMessage)-1) + '.' + CHAR(13) + CHAR(10) + ' '

RAISERROR('%s',10,1,@ErrorMessage) WITH NOWAIT

SET @Error = @@ERROR

END

SET @ErrorMessage = ''

SELECT @ErrorMessage = @ErrorMessage + QUOTENAME(DatabaseName) + ', '

FROM @SelectedObjects

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

SET @ErrorMessage = 'The following databases have been selected in the @Objects parameter, but not in the @Databases or @AvailabilityGroups parameters: ' + LEFT(@ErrorMessage,LEN(@ErrorMessage)-1) + '.' + CHAR(13) + CHAR(10) + ' '

RAISERROR('%s',10,1,@ErrorMessage) WITH NOWAIT

SET @Error = @@ERROR

END

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

--// Check @@SERVERNAME //--

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

IF @@SERVERNAME <> CAST(SERVERPROPERTY('ServerName') AS nvarchar(max)) AND SERVERPROPERTY('IsHadrEnabled') = 1

BEGIN

SET @ErrorMessage = 'The @@SERVERNAME does not match SERVERPROPERTY(''ServerName''). See ' + CASE WHEN SERVERPROPERTY('IsClustered') = 0 THEN 'https://docs.microsoft.com/en-us/sql/database-engine/install-windows/rename-a-computer-that-hosts-a-stand-alone-instance-of-sql-server' WHEN SERVERPROPERTY('IsClustered') = 1 THEN 'https://docs.microsoft.com/en-us/sql/sql-server/failover-clusters/install/rename-a-sql-server-failover-cluster-instance' END + '.' + CHAR(13) + CHAR(10) + ' '

RAISERROR('%s',16,1,@ErrorMessage) WITH NOWAIT

SET @Error = @@ERROR

END

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

--// Update database order //--

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

IF @DatabaseOrder IN('DATABASE_SIZE_ASC','DATABASE_SIZE_DESC')

BEGIN

UPDATE tmpDatabases

SET DatabaseSize = (SELECT SUM(size) FROM sys.master_files WHERE [type] = 0 AND database_id = DB_ID(tmpDatabases.DatabaseName))

FROM @tmpDatabases tmpDatabases

END

IF @DatabaseOrder IN('DATABASE_LAST_GOOD_CHECK_ASC','DATABASE_LAST_GOOD_CHECK_DESC')

BEGIN

UPDATE tmpDatabases

SET LastGoodCheckDbTime = NULLIF(CAST(DATABASEPROPERTYEX (DatabaseName,'LastGoodCheckDbTime') AS datetime),'1900-01-01 00:00:00.000')

FROM @tmpDatabases tmpDatabases

END

IF @DatabaseOrder IN('REPLICA_LAST_GOOD_CHECK_ASC','REPLICA_LAST_GOOD_CHECK_DESC')

BEGIN

UPDATE tmpDatabases

SET LastCommandTime = MaxStartTime

FROM @tmpDatabases tmpDatabases

INNER JOIN (SELECT DatabaseName, MAX(StartTime) AS MaxStartTime

FROM dbo.CommandLog

WHERE CommandType = 'DBCC_CHECKDB'

AND ErrorNumber = 0

GROUP BY DatabaseName) CommandLog

ON tmpDatabases.DatabaseName = CommandLog.DatabaseName COLLATE DATABASE_DEFAULT

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

ELSE

IF @DatabaseOrder = 'DATABASE_LAST_GOOD_CHECK_ASC'

BEGIN

WITH tmpDatabases AS (

SELECT DatabaseName, [Order], ROW_NUMBER() OVER (ORDER BY LastGoodCheckDbTime ASC) AS RowNumber

FROM @tmpDatabases tmpDatabases

WHERE Selected = 1