SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
	ALTER PROCEDURE [DBCC].[InsertCheckDBDetails]
	AS
	BEGIN
		DECLARE @SQL NVARCHAR(MAX)

		SELECT @SQL = REPLACE(
							CAST(
									(
										SELECT 'USE ' + QUOTENAME(name) +';' + CHAR(13) + CHAR(10) +
										'DBCC CHECKDB WITH NO_INFOMSGS, ALL_ERRORMSGS; '
										FROM sys.databases
										FOR XML PATH('')
									) 
								AS NVARCHAR(MAX)
							),
						'&#x 0D;',CHAR(13) + CHAR(10) --REMOVE THE SPACE ON THIS LINE BETWEEN x and 0
						)
		--SELECT @SQL
		EXECUTE sp_executesql @SQL
	
		WAITFOR DELAY '00:01:00'
		
		CREATE TABLE #Error
		(
			LogDate DATETIME,
			ProcessInfo NVARCHAR(50),
			Text NVARCHAR(MAX)
		)
		
		INSERT INTO
			#Error
		EXEC sp_readerrorlog
		
		INSERT INTO
			[DBCC].CheckDBDetails
				(
					Results
				)
		SELECT
			Text
		FROM
			#Error
		WHERE
			Text LIKE 'DBCC%'
			AND
			LogDate > CONVERT(DATE,GETDATE())
		
		DROP TABLE #Error
		
		EXEC [DBCC].[CheckDBDetailsEmail]
	END
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [DBCC].[CheckDBDetailsEmail]
						AS
						BEGIN
							DECLARE @MailSubject VARCHAR(MAX)
							SET @MailSubject = 'DBCC CHECKDB results for ' + @@SERVERNAME + ' at ' + CONVERT(VARCHAR,GETDATE(),120)
							
							DECLARE @EmailAddress NVARCHAR(MAX)
							SELECT @EmailAddress = EmailAddress FROM CONFIG.AlertEmails WHERE Alert = 'DBCC.CheckDBDetailsEmailTrigger'	
							
							
							DECLARE @EmailBody NVARCHAR(MAX) = '', @Count INT, @a INT = 1
							SELECT @Count = COUNT(*) FROM [DBCC].CheckDBDetails WHERE DateCollected = (SELECT MAX(DateCollected) FROM [DBCC].CheckDBDetails)
							WHILE @a <= @Count
							BEGIN
								;WITH CTE AS
								(
									SELECT
										ROW_NUMBER() OVER (ORDER BY DateCollected) AS RowNum,
										Results
									FROM
										[DBCC].CheckDBDetails
									WHERE
										DateCollected = (SELECT MAX(DateCollected) FROM [DBCC].CheckDBDetails)
								)							
								SELECT @EmailBody = @EmailBody + Results + CHAR(13) + CHAR(10) FROM CTE WHERE RowNum = @a
								SET @a = @a + 1
							END	
							
							IF @EmailBody <> ''
							BEGIN					
							EXEC
								msdb.dbo.sp_send_dbmail 
									@profile_name = 'Instance01-SQLAlerts', 
									@recipients = @EmailAddress,
									@Subject = @MailSubject,
									@Body = @EmailBody
							END
GO

CREATE TABLE [DBCC].[CheckDBDetails](
	[ID] [bigint] IDENTITY(1,1) NOT NULL,
	[DateCollected] [datetime] NULL,
	[Results] [nvarchar](max) NULL,
 CONSTRAINT [PK_DBCC_CheckDBDetails] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON)
)	
GO