Declare @cmd Varchar (1000),@profile_name Varchar(1000), @description Varchar (500)
DECLARE @xml NVARCHAR(MAX), @body NVARCHAR(MAX)
SET @xml = CAST(( SELECT @@Servername As 'td',' ',Name AS 'td' ,'' ,role_desc as 'td' ,'',operational_state_desc as 'td'
from Sys.availability_groups_cluster AS AGC Inner Join Sys.dm_hadr_availability_replica_cluster_states As RCS
on RCS.group_id=AGC.group_id Inner Join Sys.dm_hadr_availability_replica_states As ARS on ARS.replica_id=RCS.replica_id
Inner Join Sys.availability_group_listeners AS AGL
ON AGL.group_id=ARS.group_id
Where operational_state_desc is not NULL
order by 3 FOR XML PATH('tr'), ELEMENTS ) AS NVARCHAR(MAX))
SET @body ='<html><body><H3> Availability Groups status </H3>
<table border = 1>
<tr>
<th> Server Name </th> <th> Availability Groups </th> <th> Role </th> <th>Status </th> </tr>'
SET @body = @body + @xml +'</table></body></html>'
Set @cmd= @@SERVERNAME + ': Availability Groups status'
SELECT Top 1 @profile_name=name FROM msdb.dbo.sysmail_profile
EXEC msdb.dbo.sp_send_dbmail
@profile_name = @profile_name, -- replace with your SQL Database Mail Profile
@body = @body,
@body_format ='HTML',
--@recipients = 'ajeyudu@sqldbanow.com', -- replace with your email address
@recipients = 'IT.ESA-DBA@sqldbanow.com', -- replace with your email address
@subject = @cmd