Blocking Alerts

Set NOCOUNT ON
if((select COUNT(spid) from sys.sysprocesses where blocked<>0)>0)
begin

DECLARE @tableHTML  NVARCHAR(MAX) ; 

SET @tableHTML = 
    N'<H1>Blocking Information</H1>' + 
    CAST ( ( SELECT spid,       ' is blocked by  ', 
                    blocked, ' on Database ', 
                    DB_NAME(dbid), ' from application server ', 
                    hostname, ' through application name ', 
                    program_name, ' and login is ', 
                    loginame 
              from master.sys.sysprocesses where blocked >0
              FOR XML PATH('tr'), TYPE
              )
              AS NVARCHAR(MAX) ) + 
    N'</table>' ;
    --select @tableHTML

exec msdb..sp_send_dbmail @profile_name= 'DBA' 
    ,  @recipients= 'xxx@xxx.com' 
    
    ,  @subject= 'Blocking Alerts '  
    ,  @body= @tableHTML,
    @body_format = 'HTML' ;
end

DB Status Check

DB Status Check:

set nocount ON
select getdate()
declare @name VARCHAR(50),@read varchar(1500),@standby varchar(1500),@dbname1 nvarchar(1500),@state nvarchar(1500),@autoclose nvarchar(1500)
--SET @name=db_name()

DECLARE db_cursor CURSOR FOR 
SELECT name FROM sys.databases --where name =isNULL(@name,name)
WHERE (is_read_only>0 OR is_in_standby>0 OR state>0 or is_auto_close_on > 0) and name=isNULL(@name,name) order by name

OPEN db_cursor  
FETCH NEXT FROM db_cursor INTO @name  
WHILE @@FETCH_STATUS = 0  
BEGIN  
select @dbname1=name, @read=is_read_only,@standby=is_in_standby,@state=state,@autoclose=is_auto_close_on
  from sys.databases
  where name=@name
   --select @dbname1 +' '+@read+', '+ @close
   print '--- Start of Databse '+@dbname1
 if @standby>0
set @standby='--It is in stand by  '
ELSE
SET @standby=''
if @read>0
set @read='--It is read only, Run below query to bring back the database to normal:

ALTER DATABASE ['+@dbname1+'] SET  READ_WRITE
 
  '

ELSE
SET @read=''
IF @state>0
SET @state='--Database Is not online'
ELSE
set @state=''
IF @autoclose>0
SET @autoclose='--Database Is Auto Close, Run below query to set Auto Close Off:

ALTER DATABASE ['+@dbname1+'] SET AUTO_CLOSE OFF

'

ELSE
set @autoclose=''
print @read+ @standby + @state + @autoclose

FETCH NEXT FROM db_cursor INTO @name  
END  
CLOSE db_cursor
DEALLOCATE db_cursor