Send Alerts for Offline DBs

Script to send an alert when a DB is offline.

DECLARE @offlineDBs TABLE (name NVARCHAR(128));
DECLARE @mailProfileName NVARCHAR(128) = 'mailprofile';
DECLARE @operatorEmail NVARCHAR(100);

INSERT INTO @offlineDBs
SELECT name 
FROM sys.databases 
WHERE state_desc = 'OFFLINE';

IF EXISTS (SELECT 1 FROM @offlineDBs)
BEGIN
    DECLARE @subject NVARCHAR(100) = 'ALERT: Offline Databases Detected';
    DECLARE @body NVARCHAR(MAX) = 'The following databases are OFFLINE: ' + 
        (SELECT STRING_AGG(name, ', ') FROM @offlineDBs);
    
SELECT @operatorEmail = email_address 
FROM msdb.dbo.sysoperators 
WHERE name = 'mail';

EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'mailprofile',
    @recipients = @operatorEmail,
    @subject = @subject,
    @body = @body;

END
ELSE
BEGIN
    PRINT 'No offline databases detected.';
END