Declare @Recipient_List nvarchar(max)
Declare @body_Content nvarchar(max)
Declare @Subject nvarchar(max)
Declare @xml nvarchar(max)
set @Recipient_List = 'Yourmail_id@000.com'
set @xml = cast((select count(*) as 'td'
fromdbo.Table_duplicates for XML path('tr'),elements) as nvarchar(max))
set @body_Content = '<html><body><h3> Duplicate Records Found. Verify the table Duplicates to check the duplicate records</h3>
<table border = 1>
<tr>
<th> Total Nunber of Duplicate records </th></tr>'
set
@body_Content = @body_Content + @xml + '</table></body></html>'
EXECUTE msdb.dbo.sp_send_dbmail
@recipients = @Recipient_List,
@subject = 'Count for Duplicate records',
@body = @body_Content,
@body_format = 'HTML'
