Below you will find the code to check the Last Known Good from DBCC CHECKDB. Simply select the text and copy to the Clipboard.
–FROM: https://www.experts-exchange.com/questions/27294577/DBCC-CHECKDB-issue.html
CREATE TABLE #temp (
Id INT IDENTITY(1,1),
ParentObject VARCHAR(255),
[Object] VARCHAR(255),
Field VARCHAR(255),
[Value] VARCHAR(255)
)
INSERT INTO #temp
EXECUTE master.sys.sp_MSforeachdb ‘DBCC DBINFO ( ”?”) WITH TABLERESULTS’
;WITH CHECKDB1 AS
(
SELECT [Value],ROW_NUMBER() OVER (ORDER BY Id) AS rn1 FROM #temp WHERE Field IN (‘dbi_dbname’))
,CHECKDB2 AS ( SELECT [Value], ROW_NUMBER() OVER (ORDER BY Id) AS rn2 FROM #temp WHERE Field IN (‘dbi_dbccLastKnownGood’)
)
SELECT CHECKDB1.Value AS ‘Database Name’
, CHECKDB2.Value AS ‘Date Of Last Execution’
, DATEDIFF(Day, Checkdb2.value, Getdate()) AS ‘DaysSinceLastExecution’
FROM CHECKDB1 INNER JOIN CHECKDB2
ON rn1 =rn2
WHERE checkdb1.value <> ‘Tempdb’
ORDER BY Checkdb1.value
DROP TABLE #temp