I've seen this question a few times on the forums, and unfortunately there never is a really good answer that is easy to use. SQL Server 2000 didn't track this information internally, and even though an internal tracking mechanism was added to SQL Server 2005, it isn't the easiest thing to get for every database in a large environment. See Paul Randal's blog posting:
CHECKDB From Every Angle: When did DBCC CHECKDB last run successfully?
What I figured out when looking at this in SQL Server 2000 is that DBCC CHECKDB runs, an event is logged in the SQL Server Error Logs. So if you keep you logs long enough, and you run CHECKDB appropriately, you should be able to scan the logs to find out the last time it ran. I personally maintain twenty-eight log files on my production servers, but I roll my log over every day as a part of a monitoring process. You can find out how to accomplish this on the following article:
Monitor the SQL Server Error Log
The default for SQL Server is to only rollover the ErrorLog when the Service restarts, and to retain the last six logs on the server. So unless you have had significant problems with SQL lately, you should have at least a few months of logs available on the server. To read the log information we can use sp_readerrorlog system stored procedure in master which accepts a log number parameter as an input allowing you to search older logs as well as the active log, and output its results set into a temporary table. . To determine how many logs there are, you can call the sp_numerrorlogs system stored procedure, and output its result set into a temporary table as well.
The following is an example that works in SQL Server 2000, 2005, and 2008:
SET NOCOUNT OFF
DECLARE @sql VARCHAR(4000)
DECLARE @lognumber INT
DECLARE @logcount INT
SET @lognumber = 0
-- create temp tables to hold the information
CREATE TABLE #dbcc
(rowid INT IDENTITY PRIMARY KEY, entrytime DATETIME,
source VARCHAR(50), logentry VARCHAR(4000))
CREATE INDEX [dbcc_logentry]
ON dbo.[#dbcc](logentry)
WITH FILLFACTOR = 70
CREATE TABLE #errorlog
(logentry VARCHAR(4000), continuationrow INT)
CREATE TABLE #avail_logs
(lognum INT, logdate DATETIME, logsize INT)
-- get the number of available logs
INSERT INTO #avail_logs
EXEC sp_enumerrorlogs
SELECT @logcount = MAX(lognum)
FROM #avail_logs
WHILE @lognumber < @logcount
BEGIN
-- clear the previous logs results
TRUNCATE TABLE #errorlog
SELECT @SQL = 'exec master..sp_readerrorlog ' + CONVERT(VARCHAR(3),@lognumber)
IF @@version LIKE '%2000%'
BEGIN
-- load the data from the errorlog
INSERT INTO #errorlog (logentry, continuationrow)
EXECUTE (@SQL)
-- store each interesting entry
INSERT INTO #dbcc (entrytime, source, logentry)
SELECT
CASE WHEN LEFT(logentry, 3) = '200' THEN CONVERT(DATETIME, SUBSTRING(logentry, 0, 23))
ELSE NULL
END AS entrytime,
CASE WHEN LEFT(logentry, 3) = '200' THEN SUBSTRING(logentry, 24, 10)
ELSE NULL
END AS source,
CASE WHEN LEFT(logentry, 3) = '200' THEN SUBSTRING(logentry, 34, 300)
ELSE logentry
END AS logentry
FROM #errorlog
WHERE logentry LIKE '%DBCC CHECKDB%'
END
ELSE
BEGIN
-- load the data from the errorlog
INSERT INTO #dbcc (entrytime, source, logentry)
EXECUTE (@SQL)
-- store each interesting entry
DELETE #dbcc
WHERE logentry NOT LIKE '%DBCC CHECKDB%'
END
-- move to the next log
SET @lognumber = @lognumber + 1
END
-- output the last entry for each database
SELECT name, MAX(entrytime)
FROM sysdatabases
LEFT JOIN #dbcc ON logentry LIKE '%' + name + '%'
GROUP BY name
-- clean up temp tables
DROP TABLE #dbcc
DROP TABLE #errorlog
DROP TABLE #avail_logs
Hope it helps someone.