Send Alerts for Restoring DBs

Script to send an alert when a DB is in restoring mode.

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

INSERT INTO @restoringDBs
SELECT name 
FROM sys.databases 
WHERE state_desc = 'RESTORING';

IF EXISTS (SELECT 1 FROM @restoringDBs)
BEGIN
    DECLARE @subject NVARCHAR(100) = 'SQL ALERT: Restoring Databases Detected';
    DECLARE @body NVARCHAR(MAX) = 'The following databases are RESTORING: ' + 
        (SELECT STRING_AGG(name, ', ') FROM @restoringDBs);
    
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 Restoring databases detected.';
END