USE master;GO-- Description: Turns a table result into a formatted HTML table. Useful for emails. -- Any ORDER BY clause needs to be passed in the separate ORDER BY parameter.-- Author:zhangchuan  -- Date: 20180611-- ====================How to use=========================CREATE PROC [dbo].[sp_TableToHtml] (  @temptb nvarchar(MAX), --A temporary table to turn into HTML format. It should be begin with "#".  @orderBy nvarchar(MAX) = NULL, --An optional ORDER BY clause. It should contain the words 'ORDER BY'.  @html nvarchar(MAX) = NULL OUTPUT --The HTML output of the procedure.)ASBEGIN       SET NOCOUNT ON;    IF @orderBy IS NULL SET @orderBy = '' ELSE    SET @orderBy = REPLACE(@orderBy, '''', '''''');    DECLARE @ExecStr nvarchar(MAX)    SET @ExecStr='    SET @html =        N''<table border="1" style="font-size:12pt; font-family:verdana; text-align:right">'' +        N''<tr style="font-weight:bold">''    SET @html = @html + '    DECLARE @ColStr VARCHAR(MAX)    SELECT @ColStr=ISNULL(@ColStr+'<th>'+name+'</th>','<th>'+name+'</th>')    FROM  tempdb.sys.columns     WHERE object_id=(SELECT TOP 1 object_id FROM tempdb.sys.tables WHERE name like '%'+@temptb+'%' ORDER BY create_date DESC) ORDER BY column_id    SET @ExecStr=@ExecStr+'N'''+@ColStr+''''    SET @ExecStr=@ExecStr+'    SET @html = @html + N''</tr>''    '    DECLARE @RowStr VARCHAR(MAX)    SELECT @RowStr=ISNULL(@RowStr+'        ,td = LTRIM(ISNULL(['+name+'],0)),''''',',td = LTRIM(ISNULL(['+name+'],0)),''''')+CHAR(13)    FROM  tempdb.sys.columns     WHERE object_id=(SELECT TOP 1 object_id FROM tempdb.sys.tables WHERE name like '%'+@temptb+'%' ORDER BY create_date DESC) ORDER BY column_id        SET @ExecStr = @ExecStr+'        SET @html=@html+CAST(        (SELECT            '         + STUFF(@RowStr,1,1,'')        +'        FROM '+@temptb+' '+@orderBy+'        FOR XML PATH(''tr''), TYPE        ) AS NVARCHAR(MAX)     )    SET @html=replace(@html,''&lt;'',''<'')    SET @html=replace(@html,''&gt;'',''>'')    SET @html=@html+N''</table>''+CHAR(13)    '    --PRINT @ExecStr    EXEC sys.sp_executesql @ExecStr, N'@html nvarchar(MAX) OUTPUT', @html=@html OUTPUTENDGO

USE [master]GOIF OBJECT_ID('TEMPDB.DBO.#EndList') IS NOT NULL DROP TABLE #EndList--insert into temp tableSELECT TOP 10 *INTO #EndListFROM [DB_Monitor].[dbo].[T_dm_os_waiting_tasks]--Table to htmlDECLARE @html nvarchar(MAX);EXEC [sp_TableToHtml] @html = @html OUTPUT,  @temptb = N'#EndList', @orderBy = N'ORDER BY 1';--send the emailIF @html IS NOT NULL    EXEC msdb.dbo.sp_send_dbmail        @profile_name = 'DBA_Profile',        @recipients = 'zc_0101@163.com;',        --@blind_copy_recipients = 'zc_0101@163.com',        @subject = 'HTML email',        @body = @html,        @body_format = 'HTML'