This is something I knocked up to do the job.
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
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
And a job is set the initial proc to run every day at 3am during our daily maintenance window.
Just ensure you remove the space on the line with the comment in the code.