USE [master];
GO

/****** Object: StoredProcedure [dbo].[DatabaseIntegrityCheck] Script Date: 19/02/2016 09:58:06 ******/

–DROP PROCEDURE [dbo].[DatabaseIntegrityCheck];
–GO

/****** Object: StoredProcedure [dbo].[DatabaseIntegrityCheck] Script Date: 19/02/2016 09:58:06 ******/

SET ANSI_NULLS ON;
GO
SET QUOTED_IDENTIFIER ON;
GO
CREATE PROCEDURE [dbo].[DatabaseIntegrityCheck]
@Databases NVARCHAR(MAX), @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, @LockTimeout INT= NULL, @LogToTable NVARCHAR(MAX)= ‘N’, @Execute NVARCHAR(MAX)= ‘Y’
AS
BEGIN

—————————————————————————————————-
–// Source: https://ola.hallengren.com //–
—————————————————————————————————-

SET NOCOUNT ON;
DECLARE @StartMessage NVARCHAR(MAX);
DECLARE @EndMessage NVARCHAR(MAX);
DECLARE @DatabaseMessage NVARCHAR(MAX);
DECLARE @ErrorMessage NVARCHAR(MAX);
DECLARE @Version NUMERIC(18, 10);
DECLARE @AmazonRDS BIT;
DECLARE @Cluster NVARCHAR(MAX);
DECLARE @CurrentDBID INT;
DECLARE @CurrentDatabaseID INT;
DECLARE @CurrentDatabaseName NVARCHAR(MAX);
DECLARE @CurrentIsDatabaseAccessible BIT;
DECLARE @CurrentAvailabilityGroup NVARCHAR(MAX);
DECLARE @CurrentAvailabilityGroupRole NVARCHAR(MAX);
DECLARE @CurrentDatabaseMirroringRole NVARCHAR(MAX);
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), [Snapshot] BIT, Selected BIT, Completed BIT, PRIMARY KEY
(Selected, Completed, ID
)
);
DECLARE @tmpFileGroups TABLE
(
ID INT IDENTITY, FileGroupID INT, FileGroupName NVARCHAR(MAX), Selected BIT, Completed BIT, PRIMARY KEY
(Selected, Completed, ID
)
);
DECLARE @tmpObjects TABLE
(
ID INT IDENTITY, SchemaID INT, SchemaName NVARCHAR(MAX), ObjectID INT, ObjectName NVARCHAR(MAX), ObjectType NVARCHAR(MAX), Selected BIT, Completed BIT, PRIMARY KEY
(Selected, Completed, ID
)
);
DECLARE @SelectedDatabases TABLE
(
DatabaseName NVARCHAR(MAX), DatabaseType NVARCHAR(MAX), Selected BIT
);
DECLARE @SelectedFileGroups TABLE
(
DatabaseName NVARCHAR(MAX), FileGroupName NVARCHAR(MAX), Selected BIT
);
DECLARE @SelectedObjects TABLE
(
DatabaseName NVARCHAR(MAX), SchemaName NVARCHAR(MAX), ObjectName NVARCHAR(MAX), 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)
);
SET @AmazonRDS = CASE
WHEN DB_ID(‘rdsadmin’) IS NOT NULL
AND SUSER_SNAME(0x01) = ‘rdsa’
THEN 1
ELSE 0
END;

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

SET @StartMessage = ‘Date and time: ‘+CONVERT(NVARCHAR, GETDATE(), 120
)+CHAR(13)+CHAR(10);
SET @StartMessage = @StartMessage+’Server: ‘+CAST(SERVERPROPERTY(‘ServerName’) AS NVARCHAR
)+CHAR(13)+CHAR(10);
SET @StartMessage = @StartMessage+’Version: ‘+CAST(SERVERPROPERTY(‘ProductVersion’) AS NVARCHAR
)+CHAR(13)+CHAR(10);
SET @StartMessage = @StartMessage+’Edition: ‘+CAST(SERVERPROPERTY(‘Edition’) AS NVARCHAR
)+CHAR(13)+CHAR(10);
SET @StartMessage = @StartMessage+’Procedure: ‘+QUOTENAME(DB_NAME(DB_ID()))+’.’+
(
SELECT QUOTENAME(schemas.name)
FROM sys.schemas schemas
INNER JOIN sys.objects objects ON schemas.[schema_id] = objects.[schema_id]
WHERE [object_id] = @@PROCID
)+’.’+QUOTENAME(OBJECT_NAME(@@PROCID))+CHAR(13)+CHAR(10);
SET @StartMessage = @StartMessage+’Parameters: @Databases = ‘+ISNULL(””+REPLACE(@Databases, ””, ”””)+””, ‘NULL’);
SET @StartMessage = @StartMessage+’, @CheckCommands = ‘+ISNULL(””+REPLACE(@CheckCommands, ””, ”””)+””, ‘NULL’);
SET @StartMessage = @StartMessage+’, @PhysicalOnly = ‘+ISNULL(””+REPLACE(@PhysicalOnly, ””, ”””)+””, ‘NULL’);
SET @StartMessage = @StartMessage+’, @NoIndex = ‘+ISNULL(””+REPLACE(@NoIndex, ””, ”””)+””, ‘NULL’);
SET @StartMessage = @StartMessage+’, @ExtendedLogicalChecks = ‘+ISNULL(””+REPLACE(@ExtendedLogicalChecks, ””, ”””)+””, ‘NULL’);
SET @StartMessage = @StartMessage+’, @TabLock = ‘+ISNULL(””+REPLACE(@TabLock, ””, ”””)+””, ‘NULL’);
SET @StartMessage = @StartMessage+’, @FileGroups = ‘+ISNULL(””+REPLACE(@FileGroups, ””, ”””)+””, ‘NULL’);
SET @StartMessage = @StartMessage+’, @Objects = ‘+ISNULL(””+REPLACE(@Objects, ””, ”””)+””, ‘NULL’);
SET @StartMessage = @StartMessage+’, @LockTimeout = ‘+ISNULL(CAST(@LockTimeout AS NVARCHAR
), ‘NULL’);
SET @StartMessage = @StartMessage+’, @LogToTable = ‘+ISNULL(””+REPLACE(@LogToTable, ””, ”””)+””, ‘NULL’);
SET @StartMessage = @StartMessage+’, @Execute = ‘+ISNULL(””+REPLACE(@Execute, ””, ”””)+””, ‘NULL’)+CHAR(13)+CHAR(10);
SET @StartMessage = @StartMessage+’Source: https://ola.hallengren.com’+CHAR(13)+CHAR(10);
SET @StartMessage = REPLACE(@StartMessage, ‘%’, ‘%%’)+’ ‘;
RAISERROR
(@StartMessage, 10, 1
) WITH NOWAIT;

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

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
(@ErrorMessage, 16, 1
) WITH NOWAIT;
SET @Error = @@ERROR;
END;
IF EXISTS
(
SELECT *
FROM sys.objects objects
INNER JOIN sys.schemas schemas ON objects.[schema_id] = schemas.[schema_id]
WHERE objects.[type] = ‘P’
AND schemas.[name] = ‘dbo’
AND objects.[name] = ‘CommandExecute’
AND (OBJECT_DEFINITION(objects.[object_id]) NOT LIKE ‘%@LogToTable%’
OR OBJECT_DEFINITION(objects.[object_id]) LIKE ‘%LOCK_TIMEOUT%’
)
)
BEGIN
SET @ErrorMessage = ‘The stored procedure CommandExecute needs to be updated. Download https://ola.hallengren.com/scripts/CommandExecute.sql.’+CHAR(13)+CHAR(10)+’ ‘;
RAISERROR
(@ErrorMessage, 16, 1
) WITH NOWAIT;
SET @Error = @@ERROR;
END;
IF @LogToTable = ‘Y’
AND NOT EXISTS
(
SELECT *
FROM sys.objects objects
INNER JOIN sys.schemas schemas ON objects.[schema_id] = schemas.[schema_id]
WHERE objects.[type] = ‘U’
AND schemas.[name] = ‘dbo’
AND objects.[name] = ‘CommandLog’
)
BEGIN
SET @ErrorMessage = ‘The table CommandLog is missing. Download https://ola.hallengren.com/scripts/CommandLog.sql.’+CHAR(13)+CHAR(10)+’ ‘;
RAISERROR
(@ErrorMessage, 16, 1
) WITH NOWAIT;
SET @Error = @@ERROR;
END;
IF SERVERPROPERTY(‘EngineEdition’) = 5
AND @Version < 12 BEGIN SET @ErrorMessage = 'The stored procedure DatabaseIntegrityCheck is not supported on this version of Azure SQL Database.'+CHAR(13)+CHAR(10)+' '; RAISERROR (@ErrorMessage, 16, 1 ) WITH NOWAIT; SET @Error = @@ERROR; END; IF @Error <> 0
BEGIN
SET @ReturnCode = @Error;
GOTO Logging;
END;

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

SET @Databases = REPLACE(@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, Selected ) AS (SELECT CASE WHEN DatabaseItem LIKE '-%' THEN RIGHT(DatabaseItem, LEN(DatabaseItem) - 1) ELSE DatabaseItem END AS DatabaseItem, CASE WHEN DatabaseItem LIKE '-%' THEN 0 ELSE 1 END AS Selected FROM Databases1 ), Databases3 (DatabaseItem, DatabaseType, Selected ) AS (SELECT CASE WHEN DatabaseItem IN ('ALL_DATABASES', 'SYSTEM_DATABASES', 'USER_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, Selected FROM Databases2 ), Databases4 (DatabaseName, DatabaseType, Selected ) AS (SELECT CASE WHEN LEFT(DatabaseItem, 1) = '[' AND RIGHT(DatabaseItem, 1) = ']' THEN PARSENAME(DatabaseItem, 1) ELSE DatabaseItem END AS DatabaseItem, DatabaseType, Selected FROM Databases3 ) INSERT INTO @SelectedDatabases (DatabaseName, DatabaseType, Selected ) SELECT DatabaseName, DatabaseType, Selected FROM Databases4 OPTION (MAXRECURSION 0 ); INSERT INTO @tmpDatabases (DatabaseName, DatabaseType, [Snapshot], Selected, Completed ) SELECT [name] AS DatabaseName, CASE WHEN name IN ('master', 'msdb', 'model' ) THEN 'S' ELSE 'U' END AS DatabaseType, CASE WHEN source_database_id IS NOT NULL THEN 1 ELSE 0 END AS [Snapshot], 0 AS Selected, 0 AS Completed FROM sys.databases ORDER BY [name] ASC; 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 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 NOT((tmpDatabases.DatabaseName = ‘tempdb’
OR tmpDatabases.[Snapshot] = 1
)
AND tmpDatabases.DatabaseName <> SelectedDatabases.DatabaseName
)
WHERE SelectedDatabases.Selected = 0;
IF @Databases IS NULL
OR 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
(@ErrorMessage, 16, 1
) WITH NOWAIT;
SET @Error = @@ERROR;
END;

—————————————————————————————————-
–// Select filegroups //–
—————————————————————————————————-

SET @FileGroups = REPLACE(@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, Selected ) AS (SELECT CASE WHEN FileGroupItem LIKE '-%' THEN RIGHT(FileGroupItem, LEN(FileGroupItem) - 1) ELSE FileGroupItem END AS FileGroupItem, CASE WHEN FileGroupItem LIKE '-%' THEN 0 ELSE 1 END AS Selected FROM FileGroups1 ), FileGroups3 (FileGroupItem, Selected ) AS (SELECT CASE WHEN FileGroupItem = 'ALL_FILEGROUPS' THEN '%.%' ELSE FileGroupItem END AS FileGroupItem, Selected FROM FileGroups2 ), FileGroups4 (DatabaseName, FileGroupName, 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, Selected FROM FileGroups3 ) INSERT INTO @SelectedFileGroups (DatabaseName, FileGroupName, Selected ) SELECT DatabaseName, FileGroupName, Selected FROM FileGroups4 OPTION (MAXRECURSION 0 ); ---------------------------------------------------------------------------------------------------- --// Select objects //-- ---------------------------------------------------------------------------------------------------- SET @Objects = REPLACE(@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, Selected ) AS (SELECT CASE WHEN ObjectItem LIKE '-%' THEN RIGHT(ObjectItem, LEN(ObjectItem) - 1) ELSE ObjectItem END AS ObjectItem, CASE WHEN ObjectItem LIKE '-%' THEN 0 ELSE 1 END AS Selected FROM Objects1 ), Objects3 (ObjectItem, Selected ) AS (SELECT CASE WHEN ObjectItem = 'ALL_OBJECTS' THEN '%.%.%' ELSE ObjectItem END AS ObjectItem, Selected FROM Objects2 ), Objects4 (DatabaseName, SchemaName, ObjectName, 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, Selected FROM Objects3 ) INSERT INTO @SelectedObjects (DatabaseName, SchemaName, ObjectName, Selected ) SELECT DatabaseName, SchemaName, ObjectName, 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
(@ErrorMessage, 16, 1
) 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
(@ErrorMessage, 16, 1
) 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
(@ErrorMessage, 16, 1
) 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
(@ErrorMessage, 16, 1
) 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
(@ErrorMessage, 16, 1
) 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
(@ErrorMessage, 16, 1
) 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
(@ErrorMessage, 16, 1
) 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 (@ErrorMessage, 16, 1 ) WITH NOWAIT; SET @Error = @@ERROR; END; IF @LogToTable NOT IN ('Y', 'N' ) OR @LogToTable IS NULL BEGIN SET @ErrorMessage = 'The value for the parameter @LogToTable is not supported.'+CHAR(13)+CHAR(10)+' '; RAISERROR (@ErrorMessage, 16, 1 ) WITH NOWAIT; SET @Error = @@ERROR; END; IF @Execute NOT IN ('Y', 'N' ) OR @Execute IS NULL BEGIN SET @ErrorMessage = 'The value for the parameter @Execute is not supported.'+CHAR(13)+CHAR(10)+' '; RAISERROR (@ErrorMessage, 16, 1 ) WITH NOWAIT; SET @Error = @@ERROR; END; IF @Error <> 0
BEGIN
SET @ErrorMessage = ‘The documentation is available at https://ola.hallengren.com/sql-server-integrity-check.html.’+CHAR(13)+CHAR(10)+’ ‘;
RAISERROR
(@ErrorMessage, 16, 1
) WITH NOWAIT;
SET @ReturnCode = @Error;
GOTO Logging;
END;

—————————————————————————————————-
–// Check Availability Group cluster name //–
—————————————————————————————————-

IF @Version >= 11
AND SERVERPROPERTY(‘EngineEdition’) <> 5
BEGIN
SELECT @Cluster = cluster_name
FROM sys.dm_hadr_cluster;
END;

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

WHILE EXISTS
(
SELECT *
FROM @tmpDatabases
WHERE Selected = 1
AND Completed = 0
)
BEGIN
SELECT TOP 1 @CurrentDBID = ID, @CurrentDatabaseName = DatabaseName
FROM @tmpDatabases
WHERE Selected = 1
AND Completed = 0
ORDER BY ID ASC;
SET @CurrentDatabaseID = DB_ID(@CurrentDatabaseName);
IF DATABASEPROPERTYEX(@CurrentDatabaseName, ‘Status’) = ‘ONLINE’
AND SERVERPROPERTY(‘EngineEdition’) <> 5
BEGIN
IF EXISTS
(
SELECT *
FROM sys.database_recovery_status
WHERE database_id = @CurrentDatabaseID
AND database_guid IS NOT NULL
)
BEGIN
SET @CurrentIsDatabaseAccessible = 1;
END;
ELSE
BEGIN
SET @CurrentIsDatabaseAccessible = 0;
END;
END;
IF @Version >= 11
AND @Cluster IS NOT NULL
BEGIN
SELECT @CurrentAvailabilityGroup = availability_groups.name, @CurrentAvailabilityGroupRole = dm_hadr_availability_replica_states.role_desc
FROM sys.databases databases
INNER JOIN sys.availability_databases_cluster availability_databases_cluster ON databases.group_database_id = availability_databases_cluster.group_database_id
INNER JOIN sys.availability_groups availability_groups ON availability_databases_cluster.group_id = availability_groups.group_id
INNER JOIN sys.dm_hadr_availability_replica_states dm_hadr_availability_replica_states ON availability_groups.group_id = dm_hadr_availability_replica_states.group_id
AND databases.replica_id = dm_hadr_availability_replica_states.replica_id
WHERE databases.name = @CurrentDatabaseName;
END;
IF SERVERPROPERTY(‘EngineEdition’) <> 5
BEGIN
SELECT @CurrentDatabaseMirroringRole = UPPER(mirroring_role_desc)
FROM sys.database_mirroring
WHERE database_id = @CurrentDatabaseID;
END;

— Set database message
SET @DatabaseMessage = ‘Date and time: ‘+CONVERT(NVARCHAR, GETDATE(), 120
)+CHAR(13)+CHAR(10);
SET @DatabaseMessage = @DatabaseMessage+’Database: ‘+QUOTENAME(@CurrentDatabaseName)+CHAR(13)+CHAR(10);
SET @DatabaseMessage = @DatabaseMessage+’Status: ‘+CAST(DATABASEPROPERTYEX(@CurrentDatabaseName, ‘Status’) AS NVARCHAR
)+CHAR(13)+CHAR(10);
SET @DatabaseMessage = @DatabaseMessage+’Standby: ‘+CASE
WHEN DATABASEPROPERTYEX(@CurrentDatabaseName, ‘IsInStandBy’) = 1
THEN ‘Yes’
ELSE ‘No’
END+CHAR(13)+CHAR(10);
SET @DatabaseMessage = @DatabaseMessage+’Updateability: ‘+CAST(DATABASEPROPERTYEX(@CurrentDatabaseName, ‘Updateability’) AS NVARCHAR
)+CHAR(13)+CHAR(10);
SET @DatabaseMessage = @DatabaseMessage+’User access: ‘+CAST(DATABASEPROPERTYEX(@CurrentDatabaseName, ‘UserAccess’) AS NVARCHAR
)+CHAR(13)+CHAR(10);
IF @CurrentIsDatabaseAccessible IS NOT NULL
SET @DatabaseMessage = @DatabaseMessage+’Is accessible: ‘+CASE
WHEN @CurrentIsDatabaseAccessible = 1
THEN ‘Yes’
ELSE ‘No’
END+CHAR(13)+CHAR(10);
SET @DatabaseMessage = @DatabaseMessage+’Recovery model: ‘+CAST(DATABASEPROPERTYEX(@CurrentDatabaseName, ‘Recovery’) AS NVARCHAR
)+CHAR(13)+CHAR(10);
IF @CurrentAvailabilityGroup IS NOT NULL
SET @DatabaseMessage = @DatabaseMessage+’Availability group: ‘+@CurrentAvailabilityGroup+CHAR(13)+CHAR(10);
IF @CurrentAvailabilityGroup IS NOT NULL
SET @DatabaseMessage = @DatabaseMessage+’Availability group role: ‘+@CurrentAvailabilityGroupRole+CHAR(13)+CHAR(10);
IF @CurrentDatabaseMirroringRole IS NOT NULL
SET @DatabaseMessage = @DatabaseMessage+’Database mirroring role: ‘+@CurrentDatabaseMirroringRole+CHAR(13)+CHAR(10);
SET @DatabaseMessage = REPLACE(@DatabaseMessage, ‘%’, ‘%%’)+’ ‘;
RAISERROR
(@DatabaseMessage, 10, 1
) WITH NOWAIT;
IF DATABASEPROPERTYEX(@CurrentDatabaseName, ‘Status’) = ‘ONLINE’
AND (@CurrentIsDatabaseAccessible = 1
OR @CurrentIsDatabaseAccessible IS NULL
)
BEGIN

— Check database
IF EXISTS
(
SELECT *
FROM @SelectedCheckCommands
WHERE CheckCommand = ‘CHECKDB’
)
BEGIN
SET @CurrentCommandType01 = ‘DBCC_CHECKDB’;
SET @CurrentCommand01 = ”;
IF @LockTimeout IS NOT NULL
SET @CurrentCommand01 = ‘SET LOCK_TIMEOUT ‘+CAST(@LockTimeout * 1000 AS NVARCHAR
)+’; ‘;
SET @CurrentCommand01 = @CurrentCommand01+’DBCC CHECKDB (‘+QUOTENAME(@CurrentDatabaseName);
IF @NoIndex = ‘Y’
SET @CurrentCommand01 = @CurrentCommand01+’, NOINDEX’;
SET @CurrentCommand01 = @CurrentCommand01+’) WITH NO_INFOMSGS, ALL_ERRORMSGS’;
IF @PhysicalOnly = ‘N’
SET @CurrentCommand01 = @CurrentCommand01+’, DATA_PURITY’;
IF @PhysicalOnly = ‘Y’
SET @CurrentCommand01 = @CurrentCommand01+’, PHYSICAL_ONLY’;
IF @ExtendedLogicalChecks = ‘Y’
SET @CurrentCommand01 = @CurrentCommand01+’, EXTENDED_LOGICAL_CHECKS’;
IF @TabLock = ‘Y’
SET @CurrentCommand01 = @CurrentCommand01+’, TABLOCK’;
EXECUTE @CurrentCommandOutput01 = [dbo].[CommandExecute] @Command = @CurrentCommand01, @CommandType = @CurrentCommandType01, @Mode = 1, @DatabaseName = @CurrentDatabaseName, @LogToTable = @LogToTable, @Execute = @Execute;
SET @Error = @@ERROR;
IF @Error <> 0
SET @CurrentCommandOutput01 = @Error;
IF @CurrentCommandOutput01 <> 0
SET @ReturnCode = @CurrentCommandOutput01;
END;

— Check filegroups
IF EXISTS
(
SELECT *
FROM @SelectedCheckCommands
WHERE CheckCommand = ‘CHECKFILEGROUP’
)
BEGIN
SET @CurrentCommand02 = ‘SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; SELECT data_space_id AS FileGroupID, name AS FileGroupName, 0 AS Selected, 0 AS Completed FROM ‘+QUOTENAME(@CurrentDatabaseName)+’.sys.filegroups filegroups WHERE [type] <> ”FX” ORDER BY CASE WHEN filegroups.name = ”PRIMARY” THEN 1 ELSE 0 END DESC, filegroups.name ASC’;
INSERT INTO @tmpFileGroups
(FileGroupID, FileGroupName, Selected, Completed
)
EXECUTE sp_executesql @statement = @CurrentCommand02;
SET @Error = @@ERROR;
IF @Error <> 0
SET @ReturnCode = @Error;
IF @FileGroups IS NULL
BEGIN
UPDATE tmpFileGroups
SET tmpFileGroups.Selected = 1
FROM @tmpFileGroups tmpFileGroups;
END;
ELSE
BEGIN
UPDATE tmpFileGroups
SET tmpFileGroups.Selected = SelectedFileGroups.Selected
FROM @tmpFileGroups tmpFileGroups
INNER JOIN @SelectedFileGroups SelectedFileGroups ON @CurrentDatabaseName LIKE REPLACE(SelectedFileGroups.DatabaseName, ‘_’, ‘[_]’)
AND tmpFileGroups.FileGroupName LIKE REPLACE(SelectedFileGroups.FileGroupName, ‘_’, ‘[_]’)
WHERE SelectedFileGroups.Selected = 1;
UPDATE tmpFileGroups
SET tmpFileGroups.Selected = SelectedFileGroups.Selected
FROM @tmpFileGroups tmpFileGroups
INNER JOIN @SelectedFileGroups SelectedFileGroups ON @CurrentDatabaseName LIKE REPLACE(SelectedFileGroups.DatabaseName, ‘_’, ‘[_]’)
AND tmpFileGroups.FileGroupName LIKE REPLACE(SelectedFileGroups.FileGroupName, ‘_’, ‘[_]’)
WHERE SelectedFileGroups.Selected = 0;
END;
WHILE EXISTS
(
SELECT *
FROM @tmpFileGroups
WHERE Selected = 1
AND Completed = 0
)
BEGIN
SELECT TOP 1 @CurrentFGID = ID, @CurrentFileGroupID = FileGroupID, @CurrentFileGroupName = FileGroupName
FROM @tmpFileGroups
WHERE Selected = 1
AND Completed = 0
ORDER BY ID ASC;

— Does the filegroup exist?
SET @CurrentCommand03 = ”;
IF @LockTimeout IS NOT NULL
SET @CurrentCommand03 = ‘SET LOCK_TIMEOUT ‘+CAST(@LockTimeout * 1000 AS NVARCHAR
)+’; ‘;
SET @CurrentCommand03 = @CurrentCommand03+’IF EXISTS(SELECT * FROM ‘+QUOTENAME(@CurrentDatabaseName)+’.sys.filegroups filegroups WHERE [type] <> ”FX” AND filegroups.data_space_id = @ParamFileGroupID AND filegroups.[name] = @ParamFileGroupName) BEGIN SET @ParamFileGroupExists = 1 END’;
EXECUTE sp_executesql @statement = @CurrentCommand03, @params = N’@ParamFileGroupID int, @ParamFileGroupName sysname, @ParamFileGroupExists bit OUTPUT’, @ParamFileGroupID = @CurrentFileGroupID, @ParamFileGroupName = @CurrentFileGroupName, @ParamFileGroupExists = @CurrentFileGroupExists OUTPUT;
SET @Error = @@ERROR;
IF @Error = 0
AND @CurrentFileGroupExists IS NULL
SET @CurrentFileGroupExists = 0;
IF @Error = 1222
BEGIN
SET @ErrorMessage = ‘The file group ‘+QUOTENAME(@CurrentFileGroupName)+’ in the database ‘+QUOTENAME(@CurrentDatabaseName)+’ is locked. It could not be checked if the filegroup exists.’+CHAR(13)+CHAR(10)+’ ‘;
SET @ErrorMessage = REPLACE(@ErrorMessage, ‘%’, ‘%%’);
RAISERROR
(@ErrorMessage, 16, 1
) WITH NOWAIT;
END;
IF @Error <> 0
BEGIN
SET @ReturnCode = @Error;
END;
IF @CurrentFileGroupExists = 1
BEGIN
SET @CurrentCommandType04 = ‘DBCC_CHECKFILEGROUP’;
SET @CurrentCommand04 = ”;
IF @LockTimeout IS NOT NULL
SET @CurrentCommand04 = ‘SET LOCK_TIMEOUT ‘+CAST(@LockTimeout * 1000 AS NVARCHAR
)+’; ‘;
SET @CurrentCommand04 = @CurrentCommand04+’USE ‘+QUOTENAME(@CurrentDatabaseName)+’; DBCC CHECKFILEGROUP (‘+QUOTENAME(@CurrentFileGroupName);
IF @NoIndex = ‘Y’
SET @CurrentCommand04 = @CurrentCommand04+’, NOINDEX’;
SET @CurrentCommand04 = @CurrentCommand04+’) WITH NO_INFOMSGS, ALL_ERRORMSGS’;
IF @PhysicalOnly = ‘Y’
SET @CurrentCommand04 = @CurrentCommand04+’, PHYSICAL_ONLY’;
IF @TabLock = ‘Y’
SET @CurrentCommand04 = @CurrentCommand04+’, TABLOCK’;
EXECUTE @CurrentCommandOutput04 = [dbo].[CommandExecute] @Command = @CurrentCommand04, @CommandType = @CurrentCommandType04, @Mode = 1, @DatabaseName = @CurrentDatabaseName, @LogToTable = @LogToTable, @Execute = @Execute;
SET @Error = @@ERROR;
IF @Error <> 0
SET @CurrentCommandOutput04 = @Error;
IF @CurrentCommandOutput04 <> 0
SET @ReturnCode = @CurrentCommandOutput04;
END;
UPDATE @tmpFileGroups
SET Completed = 1
WHERE Selected = 1
AND Completed = 0
AND ID = @CurrentFGID;
SET @CurrentFGID = NULL;
SET @CurrentFileGroupID = NULL;
SET @CurrentFileGroupName = NULL;
SET @CurrentFileGroupExists = NULL;
SET @CurrentCommand03 = NULL;
SET @CurrentCommand04 = NULL;
SET @CurrentCommandOutput04 = NULL;
SET @CurrentCommandType04 = NULL;
END;
END;

— Check disk space allocation structures
IF EXISTS
(
SELECT *
FROM @SelectedCheckCommands
WHERE CheckCommand = ‘CHECKALLOC’
)
BEGIN
SET @CurrentCommandType05 = ‘DBCC_CHECKALLOC’;
SET @CurrentCommand05 = ”;
IF @LockTimeout IS NOT NULL
SET @CurrentCommand05 = ‘SET LOCK_TIMEOUT ‘+CAST(@LockTimeout * 1000 AS NVARCHAR
)+’; ‘;
SET @CurrentCommand05 = @CurrentCommand05+’DBCC CHECKALLOC (‘+QUOTENAME(@CurrentDatabaseName);
SET @CurrentCommand05 = @CurrentCommand05+’) WITH NO_INFOMSGS, ALL_ERRORMSGS’;
IF @TabLock = ‘Y’
SET @CurrentCommand05 = @CurrentCommand05+’, TABLOCK’;
EXECUTE @CurrentCommandOutput05 = [dbo].[CommandExecute] @Command = @CurrentCommand05, @CommandType = @CurrentCommandType05, @Mode = 1, @DatabaseName = @CurrentDatabaseName, @LogToTable = @LogToTable, @Execute = @Execute;
SET @Error = @@ERROR;
IF @Error <> 0
SET @CurrentCommandOutput05 = @Error;
IF @CurrentCommandOutput05 <> 0
SET @ReturnCode = @CurrentCommandOutput05;
END;

— Check objects
IF EXISTS
(
SELECT *
FROM @SelectedCheckCommands
WHERE CheckCommand = ‘CHECKTABLE’
)
BEGIN
SET @CurrentCommand06 = ‘SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; SELECT schemas.[schema_id] AS SchemaID, schemas.[name] AS SchemaName, objects.[object_id] AS ObjectID, objects.[name] AS ObjectName, RTRIM(objects.[type]) AS ObjectType, 0 AS Selected, 0 AS Completed FROM ‘+QUOTENAME(@CurrentDatabaseName)+’.sys.objects objects INNER JOIN ‘+QUOTENAME(@CurrentDatabaseName)+’.sys.schemas schemas ON objects.schema_id = schemas.schema_id LEFT OUTER JOIN ‘+QUOTENAME(@CurrentDatabaseName)+’.sys.tables tables ON objects.object_id = tables.object_id WHERE objects.[type] IN(”U”,”V”) AND EXISTS(SELECT * FROM ‘+QUOTENAME(@CurrentDatabaseName)+’.sys.indexes indexes WHERE indexes.object_id = objects.object_id)’+CASE
WHEN @Version >= 12
THEN ‘ AND (tables.is_memory_optimized = 0 OR is_memory_optimized IS NULL)’
ELSE ”
END+’ ORDER BY schemas.name ASC, objects.name ASC’;
INSERT INTO @tmpObjects
(SchemaID, SchemaName, ObjectID, ObjectName, ObjectType, Selected, Completed
)
EXECUTE sp_executesql @statement = @CurrentCommand06;
SET @Error = @@ERROR;
IF @Error <> 0
SET @ReturnCode = @Error;
IF @Objects IS NULL
BEGIN
UPDATE tmpObjects
SET tmpObjects.Selected = 1
FROM @tmpObjects tmpObjects;
END;
ELSE
BEGIN
UPDATE tmpObjects
SET tmpObjects.Selected = SelectedObjects.Selected
FROM @tmpObjects tmpObjects
INNER JOIN @SelectedObjects SelectedObjects ON @CurrentDatabaseName LIKE REPLACE(SelectedObjects.DatabaseName, ‘_’, ‘[_]’)
AND tmpObjects.SchemaName LIKE REPLACE(SelectedObjects.SchemaName, ‘_’, ‘[_]’)
AND tmpObjects.ObjectName LIKE REPLACE(SelectedObjects.ObjectName, ‘_’, ‘[_]’)
WHERE SelectedObjects.Selected = 1;
UPDATE tmpObjects
SET tmpObjects.Selected = SelectedObjects.Selected
FROM @tmpObjects tmpObjects
INNER JOIN @SelectedObjects SelectedObjects ON @CurrentDatabaseName LIKE REPLACE(SelectedObjects.DatabaseName, ‘_’, ‘[_]’)
AND tmpObjects.SchemaName LIKE REPLACE(SelectedObjects.SchemaName, ‘_’, ‘[_]’)
AND tmpObjects.ObjectName LIKE REPLACE(SelectedObjects.ObjectName, ‘_’, ‘[_]’)
WHERE SelectedObjects.Selected = 0;
END;
WHILE EXISTS
(
SELECT *
FROM @tmpObjects
WHERE Selected = 1
AND Completed = 0
)
BEGIN
SELECT TOP 1 @CurrentOID = ID, @CurrentSchemaID = SchemaID, @CurrentSchemaName = SchemaName, @CurrentObjectID = ObjectID, @CurrentObjectName = ObjectName, @CurrentObjectType = ObjectType
FROM @tmpObjects
WHERE Selected = 1
AND Completed = 0
ORDER BY ID ASC;

— Does the object exist?
SET @CurrentCommand07 = ”;
IF @LockTimeout IS NOT NULL
SET @CurrentCommand07 = ‘SET LOCK_TIMEOUT ‘+CAST(@LockTimeout * 1000 AS NVARCHAR
)+’; ‘;
SET @CurrentCommand07 = @CurrentCommand07+’IF EXISTS(SELECT * FROM ‘+QUOTENAME(@CurrentDatabaseName)+’.sys.objects objects INNER JOIN ‘+QUOTENAME(@CurrentDatabaseName)+’.sys.schemas schemas ON objects.schema_id = schemas.schema_id LEFT OUTER JOIN ‘+QUOTENAME(@CurrentDatabaseName)+’.sys.tables tables ON objects.object_id = tables.object_id WHERE objects.[type] IN(”U”,”V”) AND EXISTS(SELECT * FROM ‘+QUOTENAME(@CurrentDatabaseName)+’.sys.indexes indexes WHERE indexes.object_id = objects.object_id)’+CASE
WHEN @Version >= 12
THEN ‘ AND (tables.is_memory_optimized = 0 OR is_memory_optimized IS NULL)’
ELSE ”
END+’ AND schemas.[schema_id] = @ParamSchemaID AND schemas.[name] = @ParamSchemaName AND objects.[object_id] = @ParamObjectID AND objects.[name] = @ParamObjectName AND objects.[type] = @ParamObjectType) BEGIN SET @ParamObjectExists = 1 END’;
EXECUTE sp_executesql @statement = @CurrentCommand07, @params = N’@ParamSchemaID int, @ParamSchemaName sysname, @ParamObjectID int, @ParamObjectName sysname, @ParamObjectType sysname, @ParamObjectExists bit OUTPUT’, @ParamSchemaID = @CurrentSchemaID, @ParamSchemaName = @CurrentSchemaName, @ParamObjectID = @CurrentObjectID, @ParamObjectName = @CurrentObjectName, @ParamObjectType = @CurrentObjectType, @ParamObjectExists = @CurrentObjectExists OUTPUT;
SET @Error = @@ERROR;
IF @Error = 0
AND @CurrentObjectExists IS NULL
SET @CurrentObjectExists = 0;
IF @Error = 1222
BEGIN
SET @ErrorMessage = ‘The object ‘+QUOTENAME(@CurrentDatabaseName)+’.’+QUOTENAME(@CurrentSchemaName)+’.’+QUOTENAME(@CurrentObjectName)+’ is locked. It could not be checked if the object exists.’+CHAR(13)+CHAR(10)+’ ‘;
SET @ErrorMessage = REPLACE(@ErrorMessage, ‘%’, ‘%%’);
RAISERROR
(@ErrorMessage, 16, 1
) WITH NOWAIT;
END;
IF @Error <> 0
BEGIN
SET @ReturnCode = @Error;
END;
IF @CurrentObjectExists = 1
BEGIN
SET @CurrentCommandType08 = ‘DBCC_CHECKTABLE’;
SET @CurrentCommand08 = ”;
IF @LockTimeout IS NOT NULL
SET @CurrentCommand08 = ‘SET LOCK_TIMEOUT ‘+CAST(@LockTimeout * 1000 AS NVARCHAR
)+’; ‘;
SET @CurrentCommand08 = @CurrentCommand08+’USE ‘+QUOTENAME(@CurrentDatabaseName)+’; DBCC CHECKTABLE (”’+QUOTENAME(@CurrentSchemaName)+’.’+QUOTENAME(@CurrentObjectName)+””;
IF @NoIndex = ‘Y’
SET @CurrentCommand08 = @CurrentCommand08+’, NOINDEX’;
SET @CurrentCommand08 = @CurrentCommand08+’) WITH NO_INFOMSGS, ALL_ERRORMSGS’;
IF @PhysicalOnly = ‘N’
SET @CurrentCommand08 = @CurrentCommand08+’, DATA_PURITY’;
IF @PhysicalOnly = ‘Y’
SET @CurrentCommand08 = @CurrentCommand08+’, PHYSICAL_ONLY’;
IF @ExtendedLogicalChecks = ‘Y’
SET @CurrentCommand08 = @CurrentCommand08+’, EXTENDED_LOGICAL_CHECKS’;
IF @TabLock = ‘Y’
SET @CurrentCommand08 = @CurrentCommand08+’, TABLOCK’;
EXECUTE @CurrentCommandOutput08 = [dbo].[CommandExecute] @Command = @CurrentCommand08, @CommandType = @CurrentCommandType08, @Mode = 1, @DatabaseName = @CurrentDatabaseName, @SchemaName = @CurrentSchemaName, @ObjectName = @CurrentObjectName, @ObjectType = @CurrentObjectType, @LogToTable = @LogToTable, @Execute = @Execute;
SET @Error = @@ERROR;
IF @Error <> 0
SET @CurrentCommandOutput08 = @Error;
IF @CurrentCommandOutput08 <> 0
SET @ReturnCode = @CurrentCommandOutput08;
END;
UPDATE @tmpObjects
SET Completed = 1
WHERE Selected = 1
AND Completed = 0
AND ID = @CurrentOID;
SET @CurrentOID = NULL;
SET @CurrentSchemaID = NULL;
SET @CurrentSchemaName = NULL;
SET @CurrentObjectID = NULL;
SET @CurrentObjectName = NULL;
SET @CurrentObjectType = NULL;
SET @CurrentObjectExists = NULL;
SET @CurrentCommand07 = NULL;
SET @CurrentCommand08 = NULL;
SET @CurrentCommandOutput08 = NULL;
SET @CurrentCommandType08 = NULL;
END;
END;

— Check catalog
IF EXISTS
(
SELECT *
FROM @SelectedCheckCommands
WHERE CheckCommand = ‘CHECKCATALOG’
)
BEGIN
SET @CurrentCommandType09 = ‘DBCC_CHECKCATALOG’;
SET @CurrentCommand09 = ”;
IF @LockTimeout IS NOT NULL
SET @CurrentCommand09 = ‘SET LOCK_TIMEOUT ‘+CAST(@LockTimeout * 1000 AS NVARCHAR
)+’; ‘;
SET @CurrentCommand09 = @CurrentCommand09+’DBCC CHECKCATALOG (‘+QUOTENAME(@CurrentDatabaseName);
SET @CurrentCommand09 = @CurrentCommand09+’) WITH NO_INFOMSGS’;
EXECUTE @CurrentCommandOutput09 = [dbo].[CommandExecute] @Command = @CurrentCommand09, @CommandType = @CurrentCommandType09, @Mode = 1, @DatabaseName = @CurrentDatabaseName, @LogToTable = @LogToTable, @Execute = @Execute;
SET @Error = @@ERROR;
IF @Error <> 0
SET @CurrentCommandOutput09 = @Error;
IF @CurrentCommandOutput09 <> 0
SET @ReturnCode = @CurrentCommandOutput09;
END;
END;

— Update that the database is completed
UPDATE @tmpDatabases
SET Completed = 1
WHERE Selected = 1
AND Completed = 0
AND ID = @CurrentDBID;

— Clear variables
SET @CurrentDBID = NULL;
SET @CurrentDatabaseID = NULL;
SET @CurrentDatabaseName = NULL;
SET @CurrentIsDatabaseAccessible = NULL;
SET @CurrentAvailabilityGroup = NULL;
SET @CurrentAvailabilityGroupRole = NULL;
SET @CurrentDatabaseMirroringRole = NULL;
SET @CurrentCommand01 = NULL;
SET @CurrentCommand02 = NULL;
SET @CurrentCommand05 = NULL;
SET @CurrentCommand06 = NULL;
SET @CurrentCommand09 = NULL;
SET @CurrentCommandOutput01 = NULL;
SET @CurrentCommandOutput05 = NULL;
SET @CurrentCommandOutput09 = NULL;
SET @CurrentCommandType01 = NULL;
SET @CurrentCommandType05 = NULL;
SET @CurrentCommandType09 = NULL;
DELETE FROM @tmpFileGroups;
DELETE FROM @tmpObjects;
END;

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

Logging:
SET @EndMessage = ‘Date and time: ‘+CONVERT(NVARCHAR, GETDATE(), 120
);
SET @EndMessage = REPLACE(@EndMessage, ‘%’, ‘%%’);
RAISERROR
(@EndMessage, 10, 1
) WITH NOWAIT;
IF @ReturnCode <> 0
BEGIN
RETURN @ReturnCode;
END;

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

END;
GO