A month ago I noticed a very neat post about finding if a given SQL Server database has been upgraded or not. The original post shows what to use and where to look for the info. In short, DBCC DBINFO reveals two fields – dbi_createversion and dbi_version, which can ultimately help us understanding if our database has been upgraded or not.
However, imagine if you need to do this for all your databases (usually a lot more than 5-6 :) ). This would not be an easy task, so I took the script I am using for checking the last successful run of DBCC (original post here) and modified it a bit, so now the output is a lot more readable.
The entire script you can find below:
CREATE TABLE #temp ( Id INT IDENTITY(1,1), ParentObject VARCHAR(255), [Object] VARCHAR(255), Field VARCHAR(255), [Value] VARCHAR(255) ) INSERT INTO #temp EXECUTE 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_createVersion')) ,CHECKDB3 AS ( SELECT [Value], ROW_NUMBER() OVER (ORDER BY ID) AS rn3 FROM #temp WHERE Field IN ('dbi_version') ) SELECT CHECKDB1.Value AS DatabaseName , CHECKDB2.Value AS DatabaseCreateVersion , CHECKDB3.Value AS DatabaseCurrentVersion , CASE WHEN CHECKDB2.Value < CHECKDB3.Value THEN 'Yes' ELSE 'No' END as DatabaseUpgradedStatus FROM CHECKDB1 INNER JOIN CHECKDB2 ON rn1 =rn2 INNER JOIN CHECKDB3 ON rn2=rn3 DROP TABLE #temp
Probably you wonder why do you need to know this info – to be honest, so far I do not have a reason besides that it is cool and geeky. Have fun!