Inserting DBCC CHECKDB Results Into A Table

Here is the table script (this script is for the SQL Server 2005 CHECKDB output)

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[CheckDBResult](
[ServerName] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Error] [int] NULL,
[Level] [int] NULL,
[State] [int] NULL,
[MessageText] [varchar](7000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[RepairLevel] [int] NULL,
[Status] [int] NULL,
[DbId] [int] NULL,
[Id] [int] NULL,
[IndId] [int] NULL,
[PartitionId] [int] NULL,
[AllocUnitId] [int] NULL,
[File] [int] NULL,
[Page] [int] NULL,
[Slot] [int] NULL,
[RefFile] [int] NULL,
[RefPage] [int] NULL,
[RefSlot] [int] NULL,
[Allocation] [int] NULL,
[insert_date] [datetime] NOT NULL CONSTRAINT [DF_CheckDBResult_insert_date] DEFAULT (getdate())
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF

Here is the CREATE PROCEDURE script that utilizes the CHECKDB 2005 output and here is the T-SQL syntax to execute the procedure: EXEC mDBMaintenance ‘Lumiere’ <- server name to run the script against

EXEC Admin_GetCheckDBResults ’server name’

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[Admin_GetCheckDBResults]
@ServerName VARCHAR(100)

AS
DECLARE @Database SYSNAME

– Remove Previous Weeks Results
TRUNCATE TABLE DBA_Admin.dbo.CheckDBResult

DECLARE cDatabases CURSOR FOR
SELECT [name] from master..sysdatabases AS sdb
WHERE sdb.[name] NOT IN (‘tempdb’)
OPEN cDatabases
FETCH FROM cdatabases INTO @Database
WHILE ( @@FETCH_STATUS = 0)
BEGIN
SET NOCOUNT ON

INSERT INTO DBA_Admin.dbo.CheckDBResult(
Error,
[Level],
[State],
MessageText,
RepairLevel,
[Status],
[DbId],
Id,
IndId,
PartitionId, — specific to SQL Server 2005, remove for SQL Server 2000 results
AllocUnitId, — specific to SQL Server 2005, remove for SQL Server 2000 results
[File],
Page,
Slot,
RefFile,
RefPage,
RefSlot,
Allocation
)
EXEC (‘DBCC CHECKDB(”’ + @Database + ”’) WITH TABLERESULTS’)

FETCH FROM cdatabases INTO @Database
END
CLOSE cDatabases
DEALLOCATE cDatabases

– Remove all details except CHECKDB information
DELETE
FROM DBA_Admin.dbo.CheckDBResult
WHERE MessageText NOT LIKE ‘CHECKDB%’

– Update server Name
UPDATE DBA_Admin.dbo.CheckDBResult
SET ServerName = @ServerName

– Select Statment To Return Rows
SELECT ServerName,
MessageText,
Error,
[Level],
[State],
RepairLevel,
[Status],
[DbId],
Id,
IndId,
PartitionId, – specific to SQL Server 2005, remove for SQL Server 2000 results
AllocUnitId, – specific to SQL Server 2005, remove for SQL Server 2000 results
[File],
Page,
Slot,
RefFile,
RefPage,
RefSlot,
Allocation,
Insert_Date
FROM DBA_Admin.dbo.CheckDBResult
GO

Now as you can see I only want to see the last line in the DBCC CHECKDB command… however, you can modify it keep all the detail from the execution. I hope this code helps and let us know how you are using it!