I really like this script but added the print output to allow me to execute a checkdb physical_only on all of the databases.
/*
Author: Kyle Neier
Date: 3/17/2008
Description: Examines the "boot page" of each database to
express when the last successful CheckDB was performed
*/
SET NOCOUNT ON
CREATE TABLE #DBInfo_LastKnownGoodCheckDB
(
ParentObject varchar(1000) NULL,
Object varchar(1000) NULL,
Field varchar(1000) NULL,
Value varchar(1000) NULL,
DatabaseName varchar(1000) NULL
)
DECLARE csrDatabases CURSOR FAST_FORWARD LOCAL FOR
SELECT name FROM sys.databases WHERE name NOT IN ('tempdb')
OPEN csrDatabases
DECLARE
@DatabaseName varchar(1000),
@SQL varchar(8000)
FETCH NEXT FROM csrDatabases INTO @DatabaseName
WHILE @@FETCH_STATUS = 0
BEGIN
--Create dynamic SQL to be inserted into temp table
SET @SQL = 'DBCC DBINFO (' + CHAR(39) + @DatabaseName + CHAR(39) + ') WITH TABLERESULTS'
--Create Statement to execute a physical only checkdb
print '-------------------------------------------------------------------
dbcc checkdb(' + char(39) + @DatabaseName + char(39) + ') with physical_only;
-------------------------------------------------------------------
/*'
--Insert the results of the DBCC DBINFO command into the temp table
INSERT INTO #DBInfo_LastKnownGoodCheckDB
(ParentObject, Object, Field, Value) EXEC(@SQL)
--Set the database name where it has yet to be set
UPDATE #DBInfo_LastKnownGoodCheckDB
SET DatabaseName = @DatabaseName
WHERE DatabaseName IS NULL
print '*/'
FETCH NEXT FROM csrDatabases INTO @DatabaseName
END
--Get rid of the rows that I don't care about
DELETE FROM #DBInfo_LastKnownGoodCheckDB
WHERE Field <> 'dbi_dbccLastKnownGood'
SELECT
DatabaseName,
CAST(Value AS datetime) AS LastGoodCheckDB,
DATEDIFF(dd, CAST(Value AS datetime), GetDate()) AS DaysSinceGoodCheckDB,
DATEDIFF(hh, CAST(Value AS datetime), GetDate()) AS HoursSinceGoodCheckDB
FROM #DBInfo_LastKnownGoodCheckDB
ORDER BY DatabaseName
DROP TABLE #DBInfo_LastKnownGoodCheckDB
~DH