spaghettidba/dba_RunCheckDB.sql

## dba_RunCheckDB.sql
-- http://spaghettidba.com/2011/11/28/email-alert-dbcc-checkdb/
-- You have a TOOLS database, don't you?
-- If not, create it: you'll thank me later.
USE TOOLS;
GO

IF NOT EXISTS( SELECT 1 FROM sys.schemas WHERE name = 'maint')
    EXEC('CREATE SCHEMA maint');
GO

-- =============================================
-- Author:      Gianluca Sartori - spaghettidba
-- Create date: 2011-06-30
-- Description: Runs DBCC CHECKDB on the database(s) specified
--              and returns a table result that can be used in
--              reporting and alerting.
-- =============================================
CREATE PROCEDURE [maint].[dba_runCHECKDB]
    @dbName           nvarchar(max) = NULL, -- Database name. If NULL, will check all databases
    @PHYSICAL_ONLY    bit     = 0,          -- Set to 1 to perform physical check only. Defaults to 0.
    @allMessages      bit     = 0,          -- Set to 1 to return all the messages generated by DBCC
                                            -- Set to 0 to return one summary message for each database (default)
    @dbmail_profile   sysname = NULL,       -- DBMail profile to use when sending the results
    @dbmail_recipient sysname = NULL,       -- DBMail recipient
    @log_to_table     bit     = 0,          -- Set to 1 to enable logging to table DBCC_CHECKDB_HISTORY
    @help             bit     = 0
AS
BEGIN

    SET NOCOUNT,
        XACT_ABORT,
        QUOTED_IDENTIFIER,
        ANSI_NULLS,
        ANSI_PADDING,
        ANSI_WARNINGS,
        ARITHABORT,
        CONCAT_NULL_YIELDS_NULL ON;
    SET NUMERIC_ROUNDABORT OFF;

    IF @help = 1
    BEGIN
        SELECT *
        FROM (
                      SELECT '@dbname nvarchar(max) = NULL','Database name.'
            UNION ALL SELECT '','''Database'' checks a single database'
            UNION ALL SELECT '','NULL checks all databases'
            UNION ALL SELECT '','''Database1,Database2,...DatabaseN'' checks the databases in the list'
            UNION ALL SELECT '','''^Database1,^Database2,...^DatabaseN'' checks all databases except the ones in the list'
            UNION ALL SELECT '','Combinations of positive (not prefixed with ^) and negative (prefixed with ^) elements'
            UNION ALL SELECT '','considers positive elements only. Use negative elements alone.'
            UNION ALL SELECT '',''
            UNION ALL SELECT '@PHYSICAL_ONLY bit = 0','If set to 1, performs CHECKDB WITH PHYSICAL_ONLY'
            UNION ALL SELECT '',''
            UNION ALL SELECT '@allMessages bit = 0','If set to 1, returns all the messages generated by DBCC'
            UNION ALL SELECT '','If set to 0, returns a summary message for each database'
            UNION ALL SELECT '',''
            UNION ALL SELECT '@dbmail_profile sysname = NULL','The dbmail profile to use to send out the email.'
            UNION ALL SELECT '','NULL uses the global dbmail profile'
            UNION ALL SELECT '',''
            UNION ALL SELECT '@dbmail_recipient sysname = NULL','The address that will receive the email.'
            UNION ALL SELECT '','If set to NULL disables sending the email and returns a result set.'
            UNION ALL SELECT '','If not NULL sends the email without returning a result set'
            UNION ALL SELECT '',''
            UNION ALL SELECT '@log_to_table bit = 0','If set to 1, stores the results in the table DBCC_CHECKDB_HISTORY.'
            UNION ALL SELECT '','If the table does not exists, it will create the table for you.'
            UNION ALL SELECT '','When logging to a table, a result set is not returned.'
            UNION ALL SELECT '',''
            UNION ALL SELECT '@help bit = 0','If set to 1 displays this help.'
        ) AS h (
                [-----parameter------------------------------------------------------]
            ,[-----description----------------------------------------------------------------------------------------------------------------]
        )

        RETURN
    END

    DECLARE @version int
    DECLARE @sql nvarchar(4000)
    DECLARE @ErrorMessage nvarchar(2048)
    DECLARE @body nvarchar(max)

    DECLARE @Message nvarchar(4000)
    DECLARE @Severity int
    DECLARE @State int

    -- determine major version: DBCC output can be different
    SELECT @version = CAST(REPLACE(CAST(SERVERPROPERTY('ProductVersion') AS char(2)),'.','') AS int)
    -- Clean up the @dbname parameter
    -- Remove leading/trailing spaces
    SET @dbname = LTRIM(RTRIM(@dbname))
    -- Remove leading commas
    WHILE LEFT(@dbname,1) = ','
    SET @dbname = SUBSTRING(@dbname, 2, LEN(@dbname))
    -- Remove trailing commas
    WHILE RIGHT(@dbname,1) = ','
    SET @dbname = SUBSTRING(@dbname, 1, LEN(@dbname)-1)

    IF OBJECT_ID('tempdb..##DBCC_OUTPUT') IS NOT NULL
        DROP TABLE ##DBCC_OUTPUT

     -- SQL2005, SQL2008, SQL2008R2 produce this output
    -- except for the columns explicitly marked as new in SQL 2012
    CREATE TABLE ##DBCC_OUTPUT(
        Error int NULL,
        [Level] int NULL,
        State int NULL,
        MessageText nvarchar(2048) NULL,
        RepairLevel nvarchar(22) NULL,
        Status int NULL,
        DbId int NULL, -- was smallint in SQL2005
        DbFragId int NULL,      -- new in SQL2012
        ObjectId int NULL,
        IndexId int NULL,
        PartitionId bigint NULL,
        AllocUnitId bigint NULL,
        RidDbId smallint NULL,  -- new in SQL2012
        RidPruId smallint NULL, -- new in SQL2012
        [File] smallint NULL,
        Page int NULL,
        Slot int NULL,
        RefDbId smallint NULL,  -- new in SQL2012
        RefPruId smallint NULL, -- new in SQL2012
        RefFile smallint NULL,
        RefPage int NULL,
        RefSlot int NULL,
        Allocation smallint NULL
    )

    -- Add a computed column
    ALTER TABLE ##DBCC_OUTPUT ADD Outcome AS
        CASE
            WHEN Error = 8989 AND MessageText LIKE '%0 allocation errors and 0 consistency errors%' THEN 0
            WHEN Error <> 8989 THEN NULL
            ELSE 1
        END

    -- Add an identity column to sort results when sending the email
    ALTER TABLE ##DBCC_OUTPUT ADD RowId int IDENTITY(1,1)

    DECLARE @localTran bit
    IF @@TRANCOUNT = 0
    BEGIN
        SET @localTran = 1
        BEGIN TRANSACTION LocalTran
    END

    BEGIN TRY

        -- Create the history table if needed
        IF @log_to_table = 1
            AND OBJECT_ID('maint.DBCC_CHECKDB_HISTORY') IS NULL
        BEGIN
            SELECT TOP(0)
                RowId,
                Error,
                [Level],
                State,
                MessageText,
                RepairLevel,
                Status,
                DbId,
                DbFragId,
                CAST(NULL AS sysname) AS DatabaseName,
                ObjectId,
                IndexId,
                PartitionId,
                AllocUnitId,
                RidDbId,
                RidPruId,
                [File],
                Page,
                Slot,
                RefDbId,
                RefPruId,
                RefFile,
                RefPage,
                RefSlot,
                Allocation,
                Outcome,
                GETDATE() AS RunDate
            INTO maint.DBCC_CHECKDB_HISTORY
            FROM ##DBCC_OUTPUT

            ALTER TABLE maint.DBCC_CHECKDB_HISTORY
                ADD CONSTRAINT PK_DBCC_CHECKDB_HISTORY PRIMARY KEY CLUSTERED(RowId)
        END

        -- Open a cursor on the matching databases (version dependant)

        DECLARE c_databases CURSOR LOCAL FAST_FORWARD
        FOR
        SELECT QUOTENAME(name) AS name
        FROM master.sys.databases
        WHERE (
                @dbname IS NULL
                OR ',' + @dbname + ',' NOT LIKE '%,[^!^]%' ESCAPE '!'
                OR ',' + @dbname + ',' LIKE '%,' + name + ',%'
                OR ',' + @dbname + ',' LIKE '%,' + REPLACE(QUOTENAME(name),'[','[[]') + ',%'
            )
            AND ',' + ISNULL(@dbname,'') + ',' NOT LIKE '%,!^' + name + ',%' ESCAPE '!'
            AND ',' + ISNULL(@dbname,'') + ',' NOT LIKE '%,!^' + REPLACE(QUOTENAME(name),'[','[[]') + ',%' ESCAPE '!'
            AND state_desc = 'ONLINE'
            AND is_read_only = 0
            AND source_database_id IS NULL -- Exclude Snapshots
            AND name <> 'tempdb'

        OPEN c_databases
        FETCH NEXT FROM c_databases INTO @dbName

        WHILE @@FETCH_STATUS = 0
        BEGIN

            -- Build a SQL string
            SET @sql = 'DBCC CHECKDB('+ @dbName +') WITH TABLERESULTS, ALL_ERRORMSGS '

            IF @PHYSICAL_ONLY = 1
                SET @sql = @sql + ', PHYSICAL_ONLY '

            BEGIN TRY

                IF @version > 8 AND @version < 11
                BEGIN
                    -- SQL2005/2008 use this column list
                    INSERT INTO ##DBCC_OUTPUT (
                        Error,
                        [Level],
                        State,
                        MessageText,
                        RepairLevel,
                        Status,
                        DbId,
                        ObjectId,
                        IndexId,
                        PartitionId,
                        AllocUnitId,
                        [File],
                        Page,
                        Slot,
                        RefFile,
                        RefPage,
                        RefSlot,
                        Allocation
                    )
                    EXEC(@sql)
                END

                IF @version >= 11
                BEGIN
                    -- SQL2012 uses all columns
                    INSERT INTO ##DBCC_OUTPUT (
                        Error,
                        [Level],
                        State,
                        MessageText,
                        RepairLevel,
                        Status,
                        DbId,
                        DbFragId,
                        ObjectId,
                        IndexId,
                        PartitionId,
                        AllocUnitId,
                        RidDbId,
                        RidPruId,
                        [File],
                        Page,
                        Slot,
                        RefDbId,
                        RefPruId,
                        RefFile,
                        RefPage,
                        RefSlot,
                        Allocation
                    )
                    EXEC(@sql)
                END

            END TRY
            BEGIN CATCH
                SELECT  @ErrorMessage = 'Unable to run DBCC on database ' + @dbName + ': ' + ERROR_MESSAGE()

                INSERT INTO ##DBCC_OUTPUT (Error, MessageText)
                SELECT Error = 8989,
                       MessageText = @ErrorMessage

            END CATCH

            FETCH NEXT FROM c_databases INTO @dbName
        END

        CLOSE c_databases
        DEALLOCATE c_databases

        IF NOT EXISTS (
            SELECT 1 FROM ##DBCC_OUTPUT
        )
        BEGIN
            RAISERROR('No database matches the name specified.',10,1)
        END

        IF @log_to_table = 1
        BEGIN
            INSERT INTO maint.DBCC_CHECKDB_HISTORY (
                Error,
                [Level],
                State,
                MessageText,
                RepairLevel,
                Status,
                DbId,
                DbFragId,
                DatabaseName,
                ObjectId,
                IndexId,
                PartitionId,
                AllocUnitId,
                RidDbId,
                RidPruId,
                [File],
                Page,
                Slot,
                RefDbId,
                RefPruId,
                RefFile,
                RefPage,
                RefSlot,
                Allocation,
                Outcome,
                RunDate
            )
            SELECT
                Error,
                [Level],
                State,
                MessageText,
                RepairLevel,
                Status,
                DbId,
                DbFragId,
                DatabaseName = ISNULL(DB_NAME(DbId),'resourcedb'),
                ObjectId,
                IndexId,
                PartitionId,
                AllocUnitId,
                RidDbId,
                RidPruId,
                [File],
                Page,
                Slot,
                RefDbId,
                RefPruId,
                RefFile,
                RefPage,
                RefSlot,
                Allocation,
                Outcome,
                RunDate = GETDATE()
            FROM ##DBCC_OUTPUT
            WHERE Error = 8989
                OR @allMessages = 1
                OR DbId IN (
                    SELECT DbId
                    FROM ##DBCC_OUTPUT
                    WHERE Error = 8989
                        AND Outcome = 1
                )
        END

        -- Build the final SQL statement
        SET @sql =
            'SELECT ISNULL(DB_NAME(DbId),''resourcedb'') AS DatabaseName, ' +
                CASE @allMessages
                    WHEN 1 THEN '*'
                    ELSE 'MessageText, Outcome'
                END + '
            FROM ##DBCC_OUTPUT
            WHERE 1 = 1 ' +
            CASE @allMessages WHEN 1 THEN '' ELSE 'AND Error = 8989' END

        IF @dbmail_recipient IS NULL
        BEGIN
            -- Query DBCC output directly
            EXEC(@sql)
        END
        ELSE
        BEGIN
            -- Pipe DBCC output to a variable
            SET @sql = '
                SELECT @body = (
                    SELECT ISNULL(MessageText,'''') + char(10) AS [text()]
                    FROM ( ' + @sql + ' AND Error = 8989 ) AS src
                    WHERE Outcome = 1
                    ORDER BY 1 DESC
                    FOR XML PATH('''')
                )'

            EXEC sp_executesql @sql, N'@body nvarchar(max) OUTPUT', @body OUTPUT

            -- Send CHECKDB report
            IF @body IS NOT NULL
            BEGIN

                EXEC msdb.dbo.sp_send_dbmail
                    @profile_name = @dbmail_profile,
                    @recipients   = @dbmail_recipient,
                    @subject      = 'Consistency error found!',
                    @body         = @body,
                    @importance   = 'High',
                    @query        = '
                        SET NOCOUNT ON;
                        SELECT TOP(5000) ISNULL(DB_NAME(DbId),''resourcedb'') + '' -'' AS DatabaseName, MessageText
                        FROM ##DBCC_OUTPUT WITH (NOLOCK)
                        WHERE DbId IN (
                            SELECT DbId
                            FROM ##DBCC_OUTPUT WITH (NOLOCK)
                            WHERE Error = 8989
                                AND Outcome = 1
                        )
                        ORDER BY RowId ASC ',
                    @attach_query_result_as_file = 1,
                    @query_result_no_padding     = 1,
                    @query_result_header         = 0,
                    @exclude_query_output        = 1,
                    @query_attachment_filename   = 'DBCC_CHECKDB_Errors.log'

            END

        END

        IF @localTran = 1 AND XACT_STATE() = 1
            COMMIT TRAN LocalTran

        IF OBJECT_ID('tempdb..##DBCC_OUTPUT') IS NOT NULL
            DROP TABLE ##DBCC_OUTPUT

    END TRY
    BEGIN CATCH

        SELECT  @Message = ERROR_MESSAGE(),
                @Severity = ERROR_SEVERITY(),
                @State = ERROR_STATE()

        IF @localTran = 1 AND XACT_STATE() <> 0
            ROLLBACK TRAN

        RAISERROR ( @Message, @Severity, @State)

    END CATCH
END
	-- http://spaghettidba.com/2011/11/28/email-alert-dbcc-checkdb/
	-- You have a TOOLS database, don't you?
	-- If not, create it: you'll thank me later.
	USE TOOLS;
	GO

	IF NOT EXISTS( SELECT 1 FROM sys.schemas WHERE name = 'maint')
	EXEC('CREATE SCHEMA maint');
	GO

	-- =============================================
	-- Author: Gianluca Sartori - spaghettidba
	-- Create date: 2011-06-30
	-- Description: Runs DBCC CHECKDB on the database(s) specified
	-- and returns a table result that can be used in
	-- reporting and alerting.
	-- =============================================
	CREATE PROCEDURE [maint].[dba_runCHECKDB]
	@dbName nvarchar(max) = NULL, -- Database name. If NULL, will check all databases
	@PHYSICAL_ONLY bit = 0, -- Set to 1 to perform physical check only. Defaults to 0.
	@allMessages bit = 0, -- Set to 1 to return all the messages generated by DBCC
	-- Set to 0 to return one summary message for each database (default)
	@dbmail_profile sysname = NULL, -- DBMail profile to use when sending the results
	@dbmail_recipient sysname = NULL, -- DBMail recipient
	@log_to_table bit = 0, -- Set to 1 to enable logging to table DBCC_CHECKDB_HISTORY
	@help bit = 0
	AS
	BEGIN

	SET NOCOUNT,
	XACT_ABORT,
	QUOTED_IDENTIFIER,
	ANSI_NULLS,
	ANSI_PADDING,
	ANSI_WARNINGS,
	ARITHABORT,
	CONCAT_NULL_YIELDS_NULL ON;
	SET NUMERIC_ROUNDABORT OFF;

	IF @help = 1
	BEGIN
	SELECT *
	FROM (
	SELECT '@dbname nvarchar(max) = NULL','Database name.'
	UNION ALL SELECT '','''Database'' checks a single database'
	UNION ALL SELECT '','NULL checks all databases'
	UNION ALL SELECT '','''Database1,Database2,...DatabaseN'' checks the databases in the list'
	UNION ALL SELECT '','''^Database1,^Database2,...^DatabaseN'' checks all databases except the ones in the list'
	UNION ALL SELECT '','Combinations of positive (not prefixed with ^) and negative (prefixed with ^) elements'
	UNION ALL SELECT '','considers positive elements only. Use negative elements alone.'
	UNION ALL SELECT '',''
	UNION ALL SELECT '@PHYSICAL_ONLY bit = 0','If set to 1, performs CHECKDB WITH PHYSICAL_ONLY'
	UNION ALL SELECT '',''
	UNION ALL SELECT '@allMessages bit = 0','If set to 1, returns all the messages generated by DBCC'
	UNION ALL SELECT '','If set to 0, returns a summary message for each database'
	UNION ALL SELECT '',''
	UNION ALL SELECT '@dbmail_profile sysname = NULL','The dbmail profile to use to send out the email.'
	UNION ALL SELECT '','NULL uses the global dbmail profile'
	UNION ALL SELECT '',''
	UNION ALL SELECT '@dbmail_recipient sysname = NULL','The address that will receive the email.'
	UNION ALL SELECT '','If set to NULL disables sending the email and returns a result set.'
	UNION ALL SELECT '','If not NULL sends the email without returning a result set'
	UNION ALL SELECT '',''
	UNION ALL SELECT '@log_to_table bit = 0','If set to 1, stores the results in the table DBCC_CHECKDB_HISTORY.'
	UNION ALL SELECT '','If the table does not exists, it will create the table for you.'
	UNION ALL SELECT '','When logging to a table, a result set is not returned.'
	UNION ALL SELECT '',''
	UNION ALL SELECT '@help bit = 0','If set to 1 displays this help.'
	) AS h (
	[-----parameter------------------------------------------------------]
	,[-----description----------------------------------------------------------------------------------------------------------------]
	)

	RETURN
	END

	DECLARE @version int
	DECLARE @sql nvarchar(4000)
	DECLARE @ErrorMessage nvarchar(2048)
	DECLARE @body nvarchar(max)

	DECLARE @Message nvarchar(4000)
	DECLARE @Severity int
	DECLARE @State int

	-- determine major version: DBCC output can be different
	SELECT @version = CAST(REPLACE(CAST(SERVERPROPERTY('ProductVersion') AS char(2)),'.','') AS int)
	-- Clean up the @dbname parameter
	-- Remove leading/trailing spaces
	SET @dbname = LTRIM(RTRIM(@dbname))
	-- Remove leading commas
	WHILE LEFT(@dbname,1) = ','
	SET @dbname = SUBSTRING(@dbname, 2, LEN(@dbname))
	-- Remove trailing commas
	WHILE RIGHT(@dbname,1) = ','
	SET @dbname = SUBSTRING(@dbname, 1, LEN(@dbname)-1)

	IF OBJECT_ID('tempdb..##DBCC_OUTPUT') IS NOT NULL
	DROP TABLE ##DBCC_OUTPUT

	-- SQL2005, SQL2008, SQL2008R2 produce this output
	-- except for the columns explicitly marked as new in SQL 2012
	CREATE TABLE ##DBCC_OUTPUT(
	Error int NULL,
	[Level] int NULL,
	State int NULL,
	MessageText nvarchar(2048) NULL,
	RepairLevel nvarchar(22) NULL,
	Status int NULL,
	DbId int NULL, -- was smallint in SQL2005
	DbFragId int NULL, -- new in SQL2012
	ObjectId int NULL,
	IndexId int NULL,
	PartitionId bigint NULL,
	AllocUnitId bigint NULL,
	RidDbId smallint NULL, -- new in SQL2012
	RidPruId smallint NULL, -- new in SQL2012
	[File] smallint NULL,
	Page int NULL,
	Slot int NULL,
	RefDbId smallint NULL, -- new in SQL2012
	RefPruId smallint NULL, -- new in SQL2012
	RefFile smallint NULL,
	RefPage int NULL,
	RefSlot int NULL,
	Allocation smallint NULL
	)

	-- Add a computed column
	ALTER TABLE ##DBCC_OUTPUT ADD Outcome AS
	CASE
	WHEN Error = 8989 AND MessageText LIKE '%0 allocation errors and 0 consistency errors%' THEN 0
	WHEN Error <> 8989 THEN NULL
	ELSE 1
	END

	-- Add an identity column to sort results when sending the email
	ALTER TABLE ##DBCC_OUTPUT ADD RowId int IDENTITY(1,1)

	DECLARE @localTran bit
	IF @@TRANCOUNT = 0
	BEGIN
	SET @localTran = 1
	BEGIN TRANSACTION LocalTran
	END

	BEGIN TRY

	-- Create the history table if needed
	IF @log_to_table = 1
	AND OBJECT_ID('maint.DBCC_CHECKDB_HISTORY') IS NULL
	BEGIN
	SELECT TOP(0)
	RowId,
	Error,
	[Level],
	State,
	MessageText,
	RepairLevel,
	Status,
	DbId,
	DbFragId,
	CAST(NULL AS sysname) AS DatabaseName,
	ObjectId,
	IndexId,
	PartitionId,
	AllocUnitId,
	RidDbId,
	RidPruId,
	[File],
	Page,
	Slot,
	RefDbId,
	RefPruId,
	RefFile,
	RefPage,
	RefSlot,
	Allocation,
	Outcome,
	GETDATE() AS RunDate
	INTO maint.DBCC_CHECKDB_HISTORY
	FROM ##DBCC_OUTPUT

	ALTER TABLE maint.DBCC_CHECKDB_HISTORY
	ADD CONSTRAINT PK_DBCC_CHECKDB_HISTORY PRIMARY KEY CLUSTERED(RowId)
	END

	-- Open a cursor on the matching databases (version dependant)

	DECLARE c_databases CURSOR LOCAL FAST_FORWARD
	FOR
	SELECT QUOTENAME(name) AS name
	FROM master.sys.databases
	WHERE (
	@dbname IS NULL
	OR ',' + @dbname + ',' NOT LIKE '%,[^!^]%' ESCAPE '!'
	OR ',' + @dbname + ',' LIKE '%,' + name + ',%'
	OR ',' + @dbname + ',' LIKE '%,' + REPLACE(QUOTENAME(name),'[','[[]') + ',%'
	)
	AND ',' + ISNULL(@dbname,'') + ',' NOT LIKE '%,!^' + name + ',%' ESCAPE '!'
	AND ',' + ISNULL(@dbname,'') + ',' NOT LIKE '%,!^' + REPLACE(QUOTENAME(name),'[','[[]') + ',%' ESCAPE '!'
	AND state_desc = 'ONLINE'
	AND is_read_only = 0
	AND source_database_id IS NULL -- Exclude Snapshots
	AND name <> 'tempdb'

	OPEN c_databases
	FETCH NEXT FROM c_databases INTO @dbName

	WHILE @@FETCH_STATUS = 0
	BEGIN

	-- Build a SQL string
	SET @sql = 'DBCC CHECKDB('+ @dbName +') WITH TABLERESULTS, ALL_ERRORMSGS '

	IF @PHYSICAL_ONLY = 1
	SET @sql = @sql + ', PHYSICAL_ONLY '

	BEGIN TRY

	IF @version > 8 AND @version < 11
	BEGIN
	-- SQL2005/2008 use this column list
	INSERT INTO ##DBCC_OUTPUT (
	Error,
	[Level],
	State,
	MessageText,
	RepairLevel,
	Status,
	DbId,
	ObjectId,
	IndexId,
	PartitionId,
	AllocUnitId,
	[File],
	Page,
	Slot,
	RefFile,
	RefPage,
	RefSlot,
	Allocation
	)
	EXEC(@sql)
	END

	IF @version >= 11
	BEGIN
	-- SQL2012 uses all columns
	INSERT INTO ##DBCC_OUTPUT (
	Error,
	[Level],
	State,
	MessageText,
	RepairLevel,
	Status,
	DbId,
	DbFragId,
	ObjectId,
	IndexId,
	PartitionId,
	AllocUnitId,
	RidDbId,
	RidPruId,
	[File],
	Page,
	Slot,
	RefDbId,
	RefPruId,
	RefFile,
	RefPage,
	RefSlot,
	Allocation
	)
	EXEC(@sql)
	END

	END TRY
	BEGIN CATCH
	SELECT @ErrorMessage = 'Unable to run DBCC on database ' + @dbName + ': ' + ERROR_MESSAGE()

	INSERT INTO ##DBCC_OUTPUT (Error, MessageText)
	SELECT Error = 8989,
	MessageText = @ErrorMessage

	END CATCH

	FETCH NEXT FROM c_databases INTO @dbName
	END

	CLOSE c_databases
	DEALLOCATE c_databases

	IF NOT EXISTS (
	SELECT 1 FROM ##DBCC_OUTPUT
	)
	BEGIN
	RAISERROR('No database matches the name specified.',10,1)
	END

	IF @log_to_table = 1
	BEGIN
	INSERT INTO maint.DBCC_CHECKDB_HISTORY (
	Error,
	[Level],
	State,
	MessageText,
	RepairLevel,
	Status,
	DbId,
	DbFragId,
	DatabaseName,
	ObjectId,
	IndexId,
	PartitionId,
	AllocUnitId,
	RidDbId,
	RidPruId,
	[File],
	Page,
	Slot,
	RefDbId,
	RefPruId,
	RefFile,
	RefPage,
	RefSlot,
	Allocation,
	Outcome,
	RunDate
	)
	SELECT
	Error,
	[Level],
	State,
	MessageText,
	RepairLevel,
	Status,
	DbId,
	DbFragId,
	DatabaseName = ISNULL(DB_NAME(DbId),'resourcedb'),
	ObjectId,
	IndexId,
	PartitionId,
	AllocUnitId,
	RidDbId,
	RidPruId,
	[File],
	Page,
	Slot,
	RefDbId,
	RefPruId,
	RefFile,
	RefPage,
	RefSlot,
	Allocation,
	Outcome,
	RunDate = GETDATE()
	FROM ##DBCC_OUTPUT
	WHERE Error = 8989
	OR @allMessages = 1
	OR DbId IN (
	SELECT DbId
	FROM ##DBCC_OUTPUT
	WHERE Error = 8989
	AND Outcome = 1
	)
	END

	-- Build the final SQL statement
	SET @sql =
	'SELECT ISNULL(DB_NAME(DbId),''resourcedb'') AS DatabaseName, ' +
	CASE @allMessages
	WHEN 1 THEN '*'
	ELSE 'MessageText, Outcome'
	END + '
	FROM ##DBCC_OUTPUT
	WHERE 1 = 1 ' +
	CASE @allMessages WHEN 1 THEN '' ELSE 'AND Error = 8989' END

	IF @dbmail_recipient IS NULL
	BEGIN
	-- Query DBCC output directly
	EXEC(@sql)
	END
	ELSE
	BEGIN
	-- Pipe DBCC output to a variable
	SET @sql = '
	SELECT @body = (
	SELECT ISNULL(MessageText,'''') + char(10) AS [text()]
	FROM ( ' + @sql + ' AND Error = 8989 ) AS src
	WHERE Outcome = 1
	ORDER BY 1 DESC
	FOR XML PATH('''')
	)'

	EXEC sp_executesql @sql, N'@body nvarchar(max) OUTPUT', @body OUTPUT

	-- Send CHECKDB report
	IF @body IS NOT NULL
	BEGIN

	EXEC msdb.dbo.sp_send_dbmail
	@profile_name = @dbmail_profile,
	@recipients = @dbmail_recipient,
	@subject = 'Consistency error found!',
	@body = @body,
	@importance = 'High',
	@query = '
	SET NOCOUNT ON;
	SELECT TOP(5000) ISNULL(DB_NAME(DbId),''resourcedb'') + '' -'' AS DatabaseName, MessageText
	FROM ##DBCC_OUTPUT WITH (NOLOCK)
	WHERE DbId IN (
	SELECT DbId
	FROM ##DBCC_OUTPUT WITH (NOLOCK)
	WHERE Error = 8989
	AND Outcome = 1
	)
	ORDER BY RowId ASC ',
	@attach_query_result_as_file = 1,
	@query_result_no_padding = 1,
	@query_result_header = 0,
	@exclude_query_output = 1,
	@query_attachment_filename = 'DBCC_CHECKDB_Errors.log'

	END

	END

	IF @localTran = 1 AND XACT_STATE() = 1
	COMMIT TRAN LocalTran

	IF OBJECT_ID('tempdb..##DBCC_OUTPUT') IS NOT NULL
	DROP TABLE ##DBCC_OUTPUT

	END TRY
	BEGIN CATCH

	SELECT @Message = ERROR_MESSAGE(),
	@Severity = ERROR_SEVERITY(),
	@State = ERROR_STATE()

	IF @localTran = 1 AND XACT_STATE() <> 0
	ROLLBACK TRAN

	RAISERROR ( @Message, @Severity, @State)

	END CATCH
	END