DB Integrity & Backup Report

DECLARE 
 @vcDBNameVARCHAR(200)
,@vcExecVARCHAR(MAX)

CREATE TABLE ##dbinfo
(
 DBNameVARCHAR(100)
,ParentObjectVARCHAR(100)
,ObjectVARCHAR(100)
,FieldVARCHAR(100)
,ValueVARCHAR(100)
)

SET @vcExec = ''

SELECT @vcExec = @vcExec+
' 
INSERT INTO ##dbinfo
(
 ParentObject
,Object
,Field
,Value
)
EXEC(''DBCC DBINFO (['+name+']) WITH TABLERESULTS'')

DELETE FROM ##dbinfo
WHEREField <> ''dbi_dbccLastKnownGood''

UPDATE ##dbinfo
SETDBName = '''+name+'''
WHEREDBName IS NULL
'
FROM sys.databases
WHERE [state] = 0

EXEC (@vcExec)

SELECT 
 a.database_name
,a.FullBackupDate
,b.LogBackupDate
,sd.recovery_model_desc
,CASE
WHEN value = '1900-01-01 00:00:00.000'THEN CAST('NeverRan'AS VARCHAR)
WHEN DATEDIFF(d, value, GETDATE()) > 7THEN CAST('NotCurrent'AS VARCHAR)
 ELSE CAST('Current' AS VARCHAR)
 END AS CheckDBStatus
FROM
(
SELECT
 database_name
,MAX(backup_finish_date) FullBackupDate
FROM msdb.dbo.backupset
WHERE TYPE = 'D'
GROUP BY database_name
) a
LEFT OUTER JOIN 
(
SELECT
 database_name
,MAX(backup_finish_date) LogBackupDate
FROM msdb.dbo.backupset
WHERE TYPE = 'L'
GROUP BY database_name
) b  
ON a.database_name = b.database_name
INNER JOIN sys.databasessd ON sd.name= a.database_name
INNER JOIN ##dbinfodb ON db.DBName= a.database_name
ORDER BY a.database_name

DROP TABLE ##dbinfo