SQL Server Startup Procedure Healthcheck

USE master;
GO
ALTER PROC uspSQLStartupCheck
AS
     SET NOCOUNT ON;
     IF EXISTS
     (
         SELECT 1
         FROM   tempdb..sysobjects
         WHERE  name = '##sqlservice'
     )
         DROP TABLE ##sqlservice;
     CREATE TABLE ##sqlservice(details VARCHAR(100));
     IF EXISTS
     (
         SELECT 1
         FROM   tempdb..sysobjects
         WHERE  name = '##agentservice'
     )
         DROP TABLE ##agentservice;
     CREATE TABLE ##agentservice(details VARCHAR(100));
     WAITFOR DELAY '00:00:02';
     DECLARE @sname VARCHAR(100), @starttime VARCHAR(30);
     DECLARE @authmode VARCHAR(25), @subject VARCHAR(250);
     DECLARE @insname VARCHAR(50), @agentname VARCHAR(50);
     DECLARE @sqlstatus VARCHAR(100), @agentstatus VARCHAR(100);
     DECLARE @dbstatus VARCHAR(100), @dbdetail VARCHAR(2000);
     DECLARE @sctsql VARCHAR(200), @HTML VARCHAR(8000);
     SELECT @sname = @@SERVERNAME;
     SELECT @authmode = CASE SERVERPROPERTY('IsIntegratedSecurityOnly')
                            WHEN 1
                            THEN 'Windows'
                            ELSE 'Mixed'
                        END;
     SELECT @starttime = CONVERT( VARCHAR(30), create_date, 109)
     FROM   sys.databases
     WHERE  database_id = 2;
     IF(SERVERPROPERTY('InstanceName')) IS NOT NULL
         BEGIN
             SET @insname = 'mssql$'+CONVERT(VARCHAR(40), SERVERPROPERTY('InstanceName'));
             SET @agentname = 'sqlagent$'+CONVERT(VARCHAR(40), SERVERPROPERTY('InstanceName'));
         END;
     ELSE
         BEGIN
             SET @insname = 'mssqlserver';
             SET @agentname = 'sqlserveragent';
         END;
     DECLARE @agent NVARCHAR(512);
     SELECT @agent = COALESCE(N'SQLAgent$'+CONVERT( SYSNAME, SERVERPROPERTY('InstanceName')), N'SQLServerAgent');
     INSERT INTO ##agentservice
     EXEC master.dbo.xp_servicecontrol
          'QueryState',
          @agent;
     DECLARE @sql NVARCHAR(512);
     SELECT @sql = COALESCE(N'MSSQL$'+CONVERT( SYSNAME, SERVERPROPERTY('InstanceName')), N'SQLServer');
     INSERT INTO ##sqlservice
     EXEC master.dbo.xp_servicecontrol
          'QueryState',
          @sql;
IF EXISTS
     (
         SELECT 1
         FROM   ##sqlservice
         WHERE  details LIKE '%Running%'
     )
         SET @sqlstatus = 'Running';
     ELSE
     select  @sqlstatus =  details  from ##sqlservice
     IF EXISTS
     (
         SELECT 1
         FROM   ##agentservice
         WHERE  details LIKE '%Running%'
     )
         SET @agentstatus = 'Running';
     ELSE
     select @agentstatus = details from ##agentservice
     IF EXISTS
     (
         SELECT 1
         FROM   sys.databases
         WHERE  state_desc <> 'ONLINE'
     )
         BEGIN
             SET @dbstatus = 'Some of the database(s) are offline';
             SELECT @dbdetail = '<table border="1"><tr><th>Database Name</th><th>Database Status</th></tr><tr>';
             SELECT @dbdetail = @dbdetail+'<td  align="Center">'+name+'</td><td  align="Center">'+state_desc+'</td></tr></table>'
             FROM   sys.databases
             WHERE  state_desc <> 'ONLINE';
         END;
     ELSE
         BEGIN
             SET @dbdetail = '';
             SET @dbstatus = 'All databases are online';
         END;
     SET @subject = 'CRITICAL ALERT: Page ICS SQL DBA to check server ' + convert(varchar(128),@@servername) + ', its rebooted on ' + @starttime + '. SQL: ' + @sqlstatus  + + ' Agent: ' + @agentstatus + ' and ' + @dbstatus
     SET @HTML = '<h3>'+@sname+'</h3><br>'+'<table border="1"><tr><th>SQL Server Startup time</th><th>SQL Server Service</th><th>SQL Agent Service</th> <th>Database(s) Status</th><th>Authentication Mode</th></tr><tr><td align="Center">'+@starttime+'</td><td align="Center">'+@sqlstatus+'</td><td align="Center">'+@agentstatus+'</td><td align="Center">'+@dbstatus+'</td><td align="Center">'+@authmode+'</td></tr></table><br><br>'+@dbdetail;

if exists  (    SELECT 1
         FROM   sys.databases d, ##agentservice a, ##sqlservice s
         WHERE  d.state_desc <> 'ONLINE' or a.details not like '%running%' or s.details not like '%running%'

)

begin


/* ... problem occurs ... */

exec xp_logevent 60000,@subject,error


     EXEC msdb.dbo.sp_send_dbmail
          @recipients = 'yahoo@yahoo.com',
 @from_address = 'yahoo@yahoo.com',
          @subject = @subject,
          @body = @HTML,
 @query = '    SELECT  SysJobs.name JOB_NAME
        ,SysJobSteps.step_name STEP_NAME
        ,Job.run_status
        ,Job.message
        ,Job.ExecDate
        ,Job.run_duration
        ,Job.server
    FROM    (SELECT Instance.instance_id
        ,DBSysJobHistory.job_id
        ,DBSysJobHistory.step_id
        ,DBSysJobHistory.sql_message_id
        ,DBSysJobHistory.sql_severity
        ,DBSysJobHistory.message
        ,(CASE DBSysJobHistory.run_status
            WHEN 0 THEN ''Failed''
            WHEN 1 THEN ''Succeeded''
            WHEN 2 THEN ''Retry''
            WHEN 3 THEN ''Canceled''
            WHEN 4 THEN ''In progress''
        END) as run_status
        ,((SUBSTRING(CAST(DBSysJobHistory.run_date AS VARCHAR(8)), 5, 2) + ''/''
        + SUBSTRING(CAST(DBSysJobHistory.run_date AS VARCHAR(8)), 7, 2) + ''/''
        + SUBSTRING(CAST(DBSysJobHistory.run_date AS VARCHAR(8)), 1, 4) + '' ''
        + SUBSTRING((REPLICATE(''0'',6-LEN(CAST(DBSysJobHistory.run_time AS varchar)))
        + CAST(DBSysJobHistory.run_time AS VARCHAR)), 1, 2) + '':''
        + SUBSTRING((REPLICATE(''0'',6-LEN(CAST(DBSysJobHistory.run_time AS VARCHAR)))
        + CAST(DBSysJobHistory.run_time AS VARCHAR)), 3, 2) + '':''
        + SUBSTRING((REPLICATE(''0'',6-LEN(CAST(DBSysJobHistory.run_time as varchar)))
        + CAST(DBSysJobHistory.run_time AS VARCHAR)), 5, 2))) AS ExecDate
        ,DBSysJobHistory.run_duration
        ,DBSysJobHistory.retries_attempted
        ,DBSysJobHistory.server
        FROM msdb.dbo.sysjobhistory DBSysJobHistory
        JOIN (SELECT DBSysJobHistory.job_id
            ,DBSysJobHistory.step_id
            ,MAX(DBSysJobHistory.instance_id) as instance_id
            FROM msdb.dbo.sysjobhistory DBSysJobHistory
            GROUP BY DBSysJobHistory.job_id
            ,DBSysJobHistory.step_id
            ) AS Instance ON DBSysJobHistory.instance_id = Instance.instance_id
        WHERE DBSysJobHistory.run_status <> 1 and DBSysJobHistory.run_time > DATEADD(HH,-1,GETDATE())
        ) AS Job
    JOIN msdb.dbo.sysjobs SysJobs
       ON (Job.job_id = SysJobs.job_id)
    JOIN msdb.dbo.sysjobsteps SysJobSteps
       ON (Job.job_id = SysJobSteps.job_id AND Job.step_id = SysJobSteps.step_id)',
   @attach_query_result_as_file = 1,
          @body_format = 'HTML'
END
GO
---Enabling startup parameter for that procedure
EXEC sp_procoption
     'uspSQLStartupCheck',
     'startup',
     True;
GO