This is a piece of code I use to determine the last known good DBCC CHECKDB being run against a database.  I’m surprised this information is so tricky to find.  I’d expect it to be sitting on the database properties tab right under last known full backup.  But it’s not.  Instead it is listed as a database Info property, and we need to jump through some hoops to find it.  To save anyone else jumping through the same hoops I’ve put my script below that gives the last known DBCC CHECKDB date for all databases on a SQL Server instance.

CREATE TABLE #DBCCs
(
ID INT IDENTITY(1, 1)
PRIMARY KEY ,
ParentObject VARCHAR(255) ,
Object VARCHAR(255) ,
Field VARCHAR(255) ,
Value VARCHAR(255) ,
DbName NVARCHAR(128) NULL
)

EXEC sp_MSforeachdb N’USE [?];
INSERT #DBCCs
(ParentObject,
Object,
Field,
Value)
EXEC (”DBCC DBInfo() With TableResults, NO_INFOMSGS”);
UPDATE #DBCCs SET DbName = N”?” WHERE DbName IS NULL;’;

SELECT DbName,Value as [Last DBCC] from #DBCCs
WHERE Field = ‘dbi_dbccLastKnownGood’
–AND Value < GETDATE()-14  –Uncomment to return only databases that haven’t had a consistency check in the past 2 weeks.

Drop TABLE #DBCCs

Now, I called it ‘my code’ but in reality, like all my code, it’s based on something shamelessly stolen from smarter peoples minds and shaped to my own purpose.  In this case the core of that little snippet is in Brent Ozars Blitz Script.  If all you want is the last date of a consistency check then by all means help yourself to the above code.  But if you want to quickly identify a whole bunch of issues on a server you don’t know well then I’d suggest you take a look at what the full blitz script can do.