SQL Server alerts:

Send SQL query output as HTML table in an email body using database mail.

if object_id('tempdb..#DiskAlert') is not null
drop table #DiskAlert


select
distinct
ServerProperty('machinename') ServerName,
       volume_mount_point,
       total_bytes/1024/1024/1024 DiskSizeGB,
       available_bytes/1024/1024/1024 FreeGB,
       cast(cast((available_bytes/1024/1024/1024) as decimal(10,2))*100/(total_bytes/1024/1024/1024) as decimal(10,2)) FreePCT
INTO #DiskAlert
from sys.master_files as f  
cross apply sys.dm_os_volume_stats(f.database_id, f.file_id);


if (select count(*) from #DiskAlert where FreePCT <90)>0
begin
     declare @listOfRecipients varchar(max)
	        ,@body nvarchar(max)
			,@subject varchar(250)
			,@MAIL_BODY VARCHAR(1000)
			,@TableTail varchar(1000)

    select @subject= 'Disk Space Alert'
 


SET @TableTail = '</table></body></html>' ;

SET @MAIL_BODY ='<p style="text-align:center;font-size:30px;color:red;background-color:yellow"><b><u> Disks with less than 90 percent free space</u></b></p>' +'<table border="10" align="left" cellpadding="10" cellspacing="10" style="color:black;font-family:consolas;text-align:center;">' +
    '<tr>
    <th bgcolor=#85C1E9>ServerName</th>
	<th bgcolor=#85C1E9>Volume_Mount_Point</th>
    <th bgcolor=#85C1E9>DiskSizeGB</th>
    <th bgcolor=#85C1E9>FreeGB</th>
    <th bgcolor=#85C1E9>FreePCT</th>
    </tr>'


   

       select @body =
(
   select
   td= ServerName,
           td = volume_mount_point,  
  td=DiskSizeGB,
  td=FreeGB,
           td = cast(FreePCT as varchar)
   from #DiskAlert
   where FreePCT <90
      for XML raw('tr'), elements
)                                

select @body=@MAIL_BODY + ISNULL(@Body, '') + @TableTail

       select @listOfRecipients =
                      'Recipients email ids'
                 
             exec msdb.dbo.sp_send_dbmail
             @profile_name = 'Database mail profile name'

             ,@recipients = @listOfRecipients
             ,@subject = @subject ,
             @body=@body,
             @body_format = 'HTML'
  
  end