Email send when @objName has data. @objName should include DB name as well.
Ex) @objName = ‘DBA.dbo.vw_Job_Status’

CREATE PROCEDURE [dbo].[dmp_send_email_HTML_format]
@objName VARCHAR(255)
, @Subject VARCHAR(255)
, @PreDescription varchar(8000) = NULL
, @Recipients VARCHAR(8000)
, @importance VARCHAR(255) = ‘High’
, @profile VARCHAR(255) = ‘Public_Profile’ –eMail profile name
as
BEGIN
SET NOCOUNT ON;
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

/*
Return value @r
10001 : object is wrong
10002 : column is wrong
0 : Done or Nodata.
*/

DECLARE @tableHTML NVARCHAR(max)
, @HTML_TH NVARCHAR(max)
, @HTML_TD NVARCHAR(max)
, @sql NVARCHAR(4000)
, @param NVARCHAR(4000)
, @DBName VARCHAR(255)
, @rowcnt INT
, @r INT

DECLARE @tbl_datacheck TABLE (i INT)

SET @DBName = PARSENAME(@objName,3)

SET @r = -1

IF OBJECT_ID(@objName) IS NULL BEGIN
SET @r = 10001
GOTO ERROR;
END

IF @DBName = ” OR @DBName IS NULL BEGIN
SET @DBName = DB_NAME()
END
SET @sql = ‘select top 1 1 from ‘+ @objName

INSERT INTO @tbl_datacheck
EXEC (@sql)
SET @rowcnt = @@ROWCOUNT

IF @rowcnt <=0 BEGIN
SET @r = 0
GOTO ERROR;
END

IF OBJECT_ID('tempdb.dbo.#tbl_column') IS NOT NULL
DROP TABLE #tbl_column
CREATE TABLE #tbl_column (idx INT IDENTITY(1,1), NAME VARCHAR(255))

set @sql = '
INSERT #tbl_column
SELECT name
FROM '+@DBName+'.SYS.syscolumns
WHERE ID = OBJECT_ID(@objName)
ORDER BY colorder
'
set @param = ' @objName varchar(255) '
exec sp_executesql @sql, @param, @objName

SET @rowcnt = @@ROWCOUNT
–PRINT @rowcnt

IF @rowcnt = 0 BEGIN
SET @r = 10002
GOTO ERROR;
END

SELECT @HTML_TH =
CAST ((SELECT th = isnull(NAME,' ')
FROM #tbl_column
FOR XML PATH(''), TYPE
) AS NVARCHAR(MAX) )

–print @HTML_TH
SET @HTML_TD = ''
SELECT @HTML_TD = @HTML_TD + 'td = isnull('+NAME+','' ''), '''', '
FROM #tbl_column

SET @HTML_TD = SUBSTRING(@HTML_TD, 1, LEN(@HTML_TD)-1)

SET @sql = 'SELECT @HTML_TD = CAST ((SELECT '+ REPLACE(@HTML_TD,'','''') +'
FROM '+@objName+'
FOR XML PATH(''tr''), TYPE
) AS NVARCHAR(MAX) )
'

–PRINT @sql

EXEC sp_executesql @sql, N' @HTML_TD NVARCHAR(MAX) OUTPUT ', @HTML_TD OUTPUT

–PRINT @HTML_TD

set @Recipients = isnull(@Recipients,'NowcomDBDevTeam@nowcom.com')

set @subject = @@servername +' – ' + convert(varchar(255), getdate(), 101) + ' ' + convert(varchar(5), getdate(), 108)+' ' + @Subject

SET @tableHTML = '

h1{
font-size:17px;
}
body{
font-size:15px;
}

table{
border-collapse:collapse;
border:1px solid black;
font-size:12px;
}

table th{
font:bold;
border:1px solid black;
padding-left:2px;padding-right:2px;padding-top:1px;padding-bottom:1px;
background-color:lightblue;
font-size:12px;
}

table td{
border:1px solid black;
padding-left:2px;padding-right:2px;padding-top:1px;padding-bottom:1px
font-size:12px;
}

‘+ @subject +’
‘+ ISNULL(@PreDescription,”) + ‘

‘+ @HTML_TH +’

‘
+ @HTML_TD

set @tableHTML = @tableHTML + ‘

‘

SELECT @tableHTML = ‘From Server :’ + ISNULL(@@SERVERNAME, ”) + char(13) + char(10) + ISNULL(@tableHTML, ”);

EXEC msdb.dbo.sp_send_dbmail @profile_name = @profile
, @body_format = ‘HTML’
, @recipients = @Recipients
, @importance = @importance
, @copy_recipients =”
, @subject = @subject
, @body = @tableHTML
IF @@error 0 BEGIN
RAISERROR(‘Error in dmp_send_email_HTML_format. Failed to send Database Mail.’, 16, 1);
END
RETURN 0
ERROR:
return @r
END
Go