check database status and send email

 USE [master]

GO

/****** Object:  StoredProcedure [dbo].[Database_Status]    Script Date: 7/29/2021 12:26:36 PM ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER  PROC [dbo].[Database_Status]

AS

BEGIN 

--send email when database changes status--run every 1 min as a job 

DECLARE @state VARCHAR(10)

DECLARE @Database_Name VARCHAR(100)

DECLARE @email VARCHAR(100)

SET @state = ''

SET @email = ''

DECLARE db_cursor CURSOR FOR  

SELECT name 

FROM MASTER.dbo.sysdatabases

WHERE name NOT IN ('pportal')

OPEN db_cursor   

FETCH NEXT FROM db_cursor INTO @Database_Name   

WHILE @@FETCH_STATUS = 0   

BEGIN 

SELECT @state = 

CASE 

WHEN a.state = 0 THEN 'Online'

WHEN a.state = 1 THEN 'Restoring'

WHEN a.state = 2 THEN 'Recovery_Pending'

WHEN a.state = 4 THEN 'Suspect'

WHEN a.state = 5 THEN 'Emergency'

WHEN a.state = 6 THEN 'Offline'

WHEN a.state = 7 THEN 'Copying - SQL Azure'

WHEN a.state = 10 THEN 'Ofline_Secondary - SQL Azure'

END

FROM sys.databases a WHERE name = @Database_Name

IF @state <> 'Online'

BEGIN

SET @email = 'The database ' + @Database_Name + ' is currently : ' + @state

EXEC msdb.dbo.sp_send_dbmail

@profile_name = 'Default',

    @recipients = 'kumardba99@gmail.com',

    @body = @email,

    @subject = 'Database Status' ;

END

  FETCH NEXT FROM db_cursor INTO @Database_Name   

END 

CLOSE db_cursor   

DEALLOCATE db_cursor

END