lem_checkdb_state

USE DABB
GO

if exists
	(select	*
	  from	sys.procedures
	  where name 		= 'lem_checkdb_state'
	  and	schema_id 	= schema_id('dbo'))
	drop procedure dbo.iso_checkdb_state

create procedure [dbo].[lem_checkdb_state]
as
begin
	CREATE TABLE #tempTotal
		(    DatabaseName   varchar(255),
			Field           VARCHAR(255),
			Value           VARCHAR(255))
	CREATE TABLE #temp
		(   ParentObject    VARCHAR(255),
			Object          VARCHAR(255),
			Field           VARCHAR(255),
			Value           VARCHAR(255))
 
	EXECUTE sp_MSforeachdb '
		INSERT INTO #temp EXEC(''DBCC DBINFO ( ''''?'''') WITH TABLERESULTS'')
		INSERT INTO #tempTotal (Field, Value, DatabaseName)
		SELECT Field, Value, ''?'' FROM #temp
		TRUNCATE TABLE #temp';
 
	;WITH cte
	as (SELECT    ROW_NUMBER() OVER(PARTITION BY DatabaseName, Field ORDER BY Value DESC) AS rn,
				DatabaseName,
				Value
		  FROM    #tempTotal t1
		  WHERE (Field = 'dbi_dbccLastKnownGood'))
	SELECT    DatabaseName,
			Value as dbccLastKnownGood,
			case
				when value >= getdate()-10
					then 'OK'
					else 'WARN'
				end state
	  FROM    cte
	  WHERE (rn = 1)
	  order by    2 asc,
				1 asc   
 
	DROP TABLE #temp
	DROP TABLE #tempTotal
end
GO