SQL: When was the Last CheckDB for each Database?
Author: jason
Date: 2014-04-11 17:09:27
Category: Technical

Here's a neat script to loop through the databases and list out the last time a CheckDB was run.





Code

--Get the Last CHECKDB: DBCC CHECKDB('SQLConfig');
CREATE TABLE #CheckDB1 (ParentObject VARCHAR(255)
, [Object] VARCHAR(255)
, [Field] VARCHAR(255)
, [Value] VARCHAR(255))
CREATE TABLE #CheckDB2 ([Database] VARCHAR(255), LastValue DATETIME)

EXEC sp_MSforeachdb 'Use [?];
INSERT INTO #CheckDB1
EXEC(''DBCC DBINFO([?]) WITH TABLERESULTS'');
INSERT INTO #CheckDB2
SELECT DISTINCT DB_NAME(), MAX(Value)
FROM #CheckDB1
WHERE [Field] = ''dbi_dbccLastKnownGood''
AND DB_NAME() NOT IN (SELECT [Database] FROM #CheckDB2)
GROUP BY [Field];
TRUNCATE TABLE #CheckDB1;
';
SELECT * FROM #CheckDB2 ORDER BY LastValue DESC
DROP TABLE #CheckDB1
DROP TABLE #CheckDB2




jason @ jasonthomasfrance.com - www.masterstationlog.com - copyright 2009