As a dba, its important to know that the databases being monitored under our supervision are free from corruption and the below code helps in identifying the last ran clean DBCC CHECKDB date. This technique works only for SQL Server 2005 and above.
USE tempdb GO CREATE TABLE dbo.DBCCData ( Id INT IDENTITY (1,1) , ParentObject VARCHAR (255) , [Object] VARCHAR (255) , Field VARCHAR (255) , [Value] VARCHAR (255) ) INSERT INTO dbo.DBCCData EXECUTE SP_MSFOREACHDB'DBCC DBINFO ( ''?'') WITH TABLERESULTS'; WITH CHECKDB1 AS ( SELECT [Value],ROW_NUMBER() OVER (ORDER BY ID) AS rn1 FROM dbo.DBCCData WHERE Field IN ('dbi_dbname')) ,CHECKDB2 AS ( SELECT [Value], ROW_NUMBER() OVER (ORDER BY ID) AS rn2 FROM dbo.DBCCData WHERE Field IN ('dbi_dbccLastKnownGood') ) SELECT CHECKDB1.Value AS DatabaseName , CHECKDB2.Value AS LastRanDBCCCHECKDB FROM CHECKDB1 JOIN CHECKDB2 ON rn1 = rn2 DROP TABLE dbo.DBCCData
PS: This post is migrated from my old blog to here on 03/24/2010
Acknowledgements: Aaron Alton, Ward Pond, Paul Randal, Jonathan Kehayias