I created the following query to list the DBCC CHECKDB LastKnownGood timestamp for all Databases on a Server. Is there a better way to do this?
IF OBJECT_ID(N'tempdb..#Results') IS NOT NULL
BEGIN
DROP TABLE #Results
END
CREATE TABLE #Results (ParentObject varchar(100), Object varchar(100), Field varchar(100), Value varchar(100))
GO
INSERT INTO #Results
EXEC sp_msForEachdb @command1 = 'DBCC DBINFO (''?'') WITH TABLERESULTS'
ALTER TABLE #Results ADD ID INT IDENTITY(1, 1)
GO -- required here
SELECT r.Value as [Database], r2.Value as CheckDB_LastKnownGood
FROM #Results r
INNER JOIN #Results r2
ON r2.ID = (SELECT MIN(ID) FROM #Results WHERE Field = 'dbi_dbccLastKnownGood' AND ID > r.ID)
WHERE r.Field = 'dbi_dbname'
ORDER BY r.Value
Best Answer
I would use the PowerShell Command from the dbatools module Get-DbaLastGoodCheckDb which will enable you to check all of your servers in one go.
Here is a sample output from the command
You can get detailed information like this
and check a whole bunch of servers like this
It's really quick too. In my lab of 10 servers with 125 databases it completes in 5.3 seconds
You can read more about dbatools at https://dbatools.io