Convert a SQL query result table to an HTML table for email

Question

I am running a SQL query that returns a table of results  I want to send the table in an email using dbo sp send dbMail   Is there a straightforward way within SQL to turn a table into an HTML table  Currently  I m manually constructing it using COALESCE and putting the results into a varchar that I use as the emailBody    Is there a better way to do this

User · Answer

JustinStolle s answer in a different way   A few notes   The print statement may truncate the string to 4000 characters  but my test string for example was 9520 characters in length  The  tr th  indicates hierarchy  e g    lt tr gt  lt th gt     lt  th gt  lt  tr gt   The   name  adds fields as XML attributes  MS SQL XML concatenates fields of the same name  so null in between fields prevents that    declare  body nvarchar max   select  body   cast       select N 2    cellpadding   N 2    cellspacing   N 1    border              N Database Table   tr th   null  tr td              N Entity Count   tr th   null  tr td              N Total Rows   tr th   null              select  object name  object id    td   null                      count  distinct name    td   null                      count        td   null              from sys columns              group by object name  object id                for xml path  tr    type          for xml path  table    type           as nvarchar max    print  body  -- only shows up to 4000 characters depending

User · Answer

I tried printing Multiple Tables using Mahesh Example above  Posting for convenience of others  USE MyDataBase  DECLARE  RECORDS THAT NEED TO SEND EMAIL TABLE  ID INT IDENTITY 1 1                                                   POS ID INT                                                   POS NUM VARCHAR 100  NULL                                                   DEPARTMENT VARCHAR 100  NULL                                                   DISTRICT VARCHAR 50  NULL                                                  COST LOC VARCHAR 100  NULL                                                  EMPLOYEE NAME VARCHAR 200  NULL   INSERT INTO  RECORDS THAT NEED TO SEND EMAIL POS ID POS NUM DISTRICT COST LOC DEPARTMENT EMPLOYEE NAME   SELECT uvwpos POS ID uvwpos POS NUM uvwpos DISTRICT  uvwpos COST LOC uvwpos DEPARTMENT uvemp LAST NAME         uvemp FIRST NAME  FROM uvwPOSITIONS uvwpos LEFT JOIN uvwEMPLOYEES uvemp  on uvemp POS ID uvwpos POS ID  WHERE uvwpos ACTIVE 1 AND uvwpos POS NUM LIKE  sde  AND    RTRIM LTRIM LEFT uvwpos DEPARTMENT LEN uvwpos DEPARTMENT -1     lt  gt  RTRIM LTRIM uvwpos COST LOC     OR  uvwpos DISTRICT IS NULL   OR  uvwpos COST LOC IS NULL       DECLARE  RESULT DISTRICT ISEMPTY varchar 4000  DECLARE  RESULT COST LOC ISEMPTY varchar 4000  DECLARE  RESULT COST LOC  AND DISTRICT NOT MATCHING  varchar 4000  DECLARE  BODY NVARCHAR MAX  DECLARE  HTMLHEADER VARCHAR 100  DECLARE  HTMLFOOTER VARCHAR 100  SET  HTMLHEADER   lt html gt  lt body gt   SET  HTMLFOOTER    lt  body gt  lt  html gt   SET  RESULT DISTRICT ISEMPTY        SET  BODY   HTMLHEADER    lt H3 gt PositionNumber where District is Empty  lt  H3 gt   lt table border   1 gt    lt tr gt   lt th gt  POS ID  lt  th gt   lt th gt  POS NUM  lt  th gt   lt th gt  DEPARTMENT  lt  th gt   lt th gt  DISTRICT  lt  th gt   lt th gt  COST LOC  lt  th gt  lt  tr gt       SET  RESULT DISTRICT ISEMPTY   CAST   SELECT  POS ID  AS  td     RTRIM  POS NUM   AS  td           ISNULL LEFT DEPARTMENT LEN DEPARTMENT -1                AS  td      ISNULL  DISTRICT               AS  td     ISNULL  COST LOC                 AS  td  FROM   RECORDS THAT NEED TO SEND EMAIL  WHERE DISTRICT IS NULL FOR XML PATH  tr    ELEMENTS   AS VARCHAR MAX    SET  BODY    BODY    RESULT DISTRICT ISEMPTY    lt  table gt     DECLARE  RESULT COST LOC ISEMPTY HEADER VARCHAR 400  SET  RESULT COST LOC ISEMPTY HEADER     lt H3 gt PositionNumber where COST LOC is Empty  lt  H3 gt   lt table border   1 gt    lt tr gt   lt th gt  POS ID  lt  th gt   lt th gt  POS NUM  lt  th gt   lt th gt  DEPARTMENT  lt  th gt   lt th gt  DISTRICT  lt  th gt   lt th gt  COST LOC  lt  th gt  lt  tr gt       SET  RESULT COST LOC ISEMPTY   CAST   SELECT  POS ID  AS  td     RTRIM  POS NUM   AS  td           ISNULL LEFT DEPARTMENT LEN DEPARTMENT -1                AS  td      ISNULL  DISTRICT               AS  td     ISNULL  COST LOC                 AS  td  FROM   RECORDS THAT NEED TO SEND EMAIL  WHERE COST LOC IS NULL FOR XML PATH  tr    ELEMENTS   AS VARCHAR MAX    SET  BODY    BODY    RESULT COST LOC ISEMPTY HEADER   RESULT COST LOC ISEMPTY    lt  table gt    DECLARE  RESULT COST LOC  AND DISTRICT NOT MATCHING HEADER VARCHAR 400  SET  RESULT COST LOC  AND DISTRICT NOT MATCHING HEADER   lt H3 gt PositionNumber where Department and Cost Center are Not Macthing  lt  H3 gt   lt table border   1 gt    lt tr gt   lt th gt  POS ID  lt  th gt   lt th gt  POS NUM  lt  th gt   lt th gt  DEPARTMENT  lt  th gt   lt th gt  DISTRICT  lt  th gt   lt th gt  COST LOC  lt  th gt  lt  tr gt      SET  RESULT COST LOC  AND DISTRICT NOT MATCHING   CAST   SELECT  POS ID  AS  td     RTRIM  POS NUM   AS  td           ISNULL LEFT DEPARTMENT LEN DEPARTMENT -1                AS  td      ISNULL  DISTRICT               AS  td     ISNULL  COST LOC                 AS  td  FROM   RECORDS THAT NEED TO SEND EMAIL  WHERE   RTRIM LTRIM LEFT DEPARTMENT LEN DEPARTMENT -1     lt  gt  RTRIM LTRIM COST LOC    FOR XML PATH  tr    ELEMENTS   AS VARCHAR MAX    SET  BODY    BODY    RESULT COST LOC  AND DISTRICT NOT MATCHING HEADER   RESULT COST LOC  AND DISTRICT NOT MATCHING     lt  table gt     SET  BODY    BODY    HTMLFOOTER  USE DDDADMINISTRATION DB --SEND EMAIL exec DDDADMINISTRATION DB dbo uspSMTP NOTIFY HTML                                EmailSubject    District Department  amp  CostCenter Discrepancies                                   EmailMessage    BODY                                  ToEmailAddress     Slohani azdes gov                                   FromEmailAddress     Slohani azdes gov     EXEC msdb dbo sp send dbmail  profile name    MY POROFILE   -- replace with your SQL Database Mail Profile   body    BODY   body format   HTML    recipients    Recepients internalmail com   -- replace with your email address  subject    District Department  amp  CostCenter Discrepancies

User · Answer

Here is one way to do it from an article titled  quot Format query output into an HTML table - the easy way  archive  quot    You would need to substitute the details of your own query for the ones in this example  which gets a list of tables and a row count  declare  body varchar max   set  body   cast    select td   dbtable     lt  td gt  lt td gt     cast  entities as varchar 30        lt  td gt  lt td gt     cast  rows as varchar 30    from         select dbtable    object name  object id                 entities   count  distinct name                 rows       count            from sys columns       group by object name  object id           as d for xml path   tr     type   as varchar max     set  body     lt table cellpadding  quot 2 quot  cellspacing  quot 2 quot  border  quot 1 quot  gt                 lt tr gt  lt th gt Database Table lt  th gt  lt th gt Entity Count lt  th gt  lt th gt Total Rows lt  th gt  lt  tr gt               replace  replace   body    amp lt      lt        amp gt      gt                   lt  table gt    print  body  Once you have  body  you can then use whatever email mechanism you want

User · Answer

Here my common used script below  I use this for running scripts on two tables views with SQL job and send results as two HTML tables via mail  Ofcourse you should create mail profile before run this   DECLARE  mailfrom varchar max  DECLARE  subject varchar 100  DECLARE  tableHTML NVARCHAR MAX    tableHTML1 NVARCHAR MAX    tableHTML2 NVARCHAR MAX    mailbody NVARCHAR MAX  DECLARE  Table1 NVARCHAR MAX    Table2 NVARCHAR MAX  DECLARE  jobName varchar 100  SELECT  jobName   name from msdb  sysjobs where job id     ESCAPE NONE JOBID    -- If the result set is not empty then fill the Table1 HTML table IF  SELECT COUNT    FROM  Database   Schema   Table1    gt  0 BEGIN SET  Table1   N   SELECT  Table1    Table1     lt tr style  font-size 13px background-color  FFFFFF  gt            lt td gt     ColumnText     lt  td gt           lt td gt     CAST ColumnNumber as nvarchar 30       lt  td gt       lt  tr gt     FROM  Database   Schema   Table1  ORDER BY ColumnText ColumnNumber  SET  tableHTML1    N  lt table border  1  align  Left  cellpadding  2  cellspacing  0  style  color black font-family arial helvetica sans-serif text-align left    gt     N  lt tr style   font-size 13px font-weight  normal background   FFFFFF  gt    lt th align left gt ColumnTextHeader1 lt  th gt   lt th align left gt ColumnNumberHeader2 lt  th gt   lt  tr gt      Table1     lt  table gt   END ELSE BEGIN SET  tableHTML1   N   SET  Table1   N   END   -- If the result set is not empty then fill the Table2 HTML table IF  SELECT COUNT    FROM  Database   Schema   Table2    gt  0 BEGIN SET  Table2   N   SELECT  Table2    Table2     lt tr style  font-size 13px background-color  FFFFFF  gt            lt td gt     ColumnText     lt  td gt           lt td gt     CAST ColumnNumber as nvarchar 30       lt  td gt       lt  tr gt     FROM  Database   Schema   Table2  ORDER BY ColumnText ColumnNumber  SET  tableHTML2    N  lt table border  1  align  Left  cellpadding  2  cellspacing  0  style  color black font-family arial helvetica sans-serif text-align left    gt     N  lt tr style   font-size 13px font-weight  normal background   FFFFFF  gt    lt th align left gt ColumnTextHeader1 lt  th gt   lt th align left gt ColumnNumberHeader2 lt  th gt   lt  tr gt      Table2     lt  table gt   END ELSE BEGIN SET  tableHTML2   N   SET  Table2   N   END  SET  tableHTML    tableHTML1    tableHTML2  -- If result sets from Table1 and Table2 are empty  then don t sent mail  IF  SELECT  tableHTML   lt  gt     BEGIN SET  mailbody   N  Write mail text here lt br gt  lt br gt      tableHTML SELECT  mailfrom    SQL Server  lt     cast SERVERPROPERTY  ComputerNamePhysicalNETBIOS   as varchar 50       domain com gt   SELECT  subject   N Mail Subject  Job       jobName           EXEC msdb dbo sp send dbmail      profile name   mailprofilename        recipients    lt mailaddress domain com gt         from address    mailfrom       reply to     lt mailaddress domain com gt         subject    subject       body    mailbody       body format    HTML  --     importance    HIGH  END

User · Answer

Suppose someone found his way here and does not understand the usage of the marked answer SQL  please read mine    it is edited and works  Table staff  columns staffname staffphone and staffDOB  declare  body varchar max   --    Create the body set  body   cast    select td   dbtable     lt  td gt  lt td gt     cast  phone as varchar 30        lt  td gt  lt td gt     cast  age as varchar 30    from         select dbtable    StaffName                  phone   staffphone                 age            datepart day staffdob        from staff       group by staffname StaffPhone StaffDOB           as d for xml path   tr     type   as varchar max    set  body     lt table cellpadding  2  cellspacing  2  border  1  gt                     lt tr gt  lt th gt Database Table lt  th gt  lt th gt Entity Count lt  th gt  lt th gt Total Rows lt  th gt  lt  tr gt                   replace  replace   body    amp lt      lt        amp gt      gt                       lt table gt   print  body

User · Answer

All the other answers use variables and SET operations  Here s a way to do it within a select statement  Just drop this in as a column in your existing select        SELECT            lt table style   font-family  Verdana   font-size  10pt   gt                           lt tr bgcolor   9DBED4  gt  lt th gt col1 lt  th gt  lt th gt col2 lt  th gt  lt th gt col3 lt  th gt  lt th gt col4 lt  th gt  lt th gt col5 lt  th gt  lt  tr gt                         replace  replace  body    amp lt      lt        amp gt      gt                             lt  table gt       FROM               select cast                select td   cast col1 as varchar 5       lt  td gt  lt td align  right  gt     col2     lt  td gt  lt td gt     col3     lt  td gt  lt td align  right  gt     cast col4 as varchar 5       lt  td gt  lt td align  right  gt     cast col5 as varchar 5       lt  td gt               from                       select col1    col1                              col2   col2                              col3   col3                              col4   col4                              col5   col5                     from m LineLevel as onml                     where onml pkey   oni pkey                       as d             for xml path   tr     type   as varchar max    as body       as bodycte  as LineTable

User · Answer

This might give you some idea --   CREATE TABLE  Temp        Rank    int      Player Name    varchar  128      Ranking Points   int      Country    varchar  128      INSERT INTO  Temp SELECT 1  Rafael Nadal  12390  Spain  UNION ALL SELECT 2  Roger Federer  7965  Switzerland  UNION ALL SELECT 3  Novak Djokovic  7880  Serbia    DECLARE  xml NVARCHAR MAX  DECLARE  body NVARCHAR MAX    SET  xml   CAST   SELECT  Rank  AS  td      Player Name  AS  td              Ranking Points  AS  td      Country AS  td  FROM   Temp ORDER BY Rank  FOR XML PATH  tr    ELEMENTS   AS NVARCHAR MAX     SET  body    lt html gt  lt body gt  lt H3 gt Tennis Rankings Info lt  H3 gt   lt table border   1 gt    lt tr gt   lt th gt  Rank  lt  th gt   lt th gt  Player Name  lt  th gt   lt th gt  Ranking Points  lt  th gt   lt th gt  Country  lt  th gt  lt  tr gt         SET  body    body    xml    lt  table gt  lt  body gt  lt  html gt     EXEC msdb dbo sp send dbmail  profile name    SQL ALERTING   -- replace with your SQL Database Mail Profile   body    body   body format   HTML    recipients    bruhaspathy hotmail com   -- replace with your email address  subject    E-mail in Tabular Format      DROP TABLE  Temp

User · Answer

I made a dynamic proc which turns any random query into an HTML table  so you don t have to hardcode columns like in the other responses   -- Description  Turns a query into a formatted HTML table  Useful for emails   -- Any ORDER BY clause needs to be passed in the separate ORDER BY parameter  --                                               CREATE PROC  dbo   spQueryToHtmlTable        query nvarchar MAX   --A query to turn into HTML format  It should not include an ORDER BY clause     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    AS BEGIN      SET NOCOUNT ON     IF  orderBy IS NULL BEGIN     SET  orderBy          END    SET  orderBy   REPLACE  orderBy                    DECLARE  realQuery nvarchar MAX          DECLARE  headerRow nvarchar MAX       DECLARE  cols nvarchar MAX            SELECT   INTO  dynSql FROM       query      sub       SELECT  cols   COALESCE  cols                                     name       AS     td           FROM tempdb sys columns      WHERE object id   object id   tempdb   dynSql        ORDER BY column id       SET  cols     SET  html   CAST   SELECT       cols      FROM  dynSql      orderBy     FOR XML PATH     tr       ELEMENTS XSINIL  AS nvarchar max              EXEC sys sp executesql  cols  N   html nvarchar MAX  OUTPUT     html  html OUTPUT      SELECT  headerRow   COALESCE  headerRow                    lt th gt      name      lt  th gt         FROM tempdb sys columns      WHERE object id   object id   tempdb   dynSql        ORDER BY column id       SET  headerRow      lt tr gt       headerRow      lt  tr gt          SET  html      lt table border  1  gt       headerRow    html      lt  table gt                   EXEC sys sp executesql  realQuery  N  html nvarchar MAX  OUTPUT    html  html OUTPUT END GO   Usage   DECLARE  html nvarchar MAX   EXEC spQueryToHtmlTable  html    html OUTPUT    query   N SELECT   FROM dbo People    orderBy   N ORDER BY FirstName    EXEC msdb dbo sp send dbmail      profile name    Foo        recipients    bar baz com         subject    HTML email        body    html       body format    HTML        query no truncate   1       attach query result as file   0    Related  Here is similar code to turn any arbitrary query into a CSV string

User · Answer

based on JustinStolle code  thank you   I wanted a solution that could be generic without having to specify the column names   This sample is using the data of a temp table but of course it can be adjusted as required   Here is what I got   DECLARE  htmlTH VARCHAR MAX                 htmlTD VARCHAR MAX   --get header  columns name SELECT  htmlTH    htmlTH     lt TH gt      name     lt  TH gt   FROM tempdb sys columns WHERE object id   OBJECT ID  tempdb dbo  results    --convert table to XML PATH  ELEMENTS XSINIL is used to include NULL values SET  htmlTD    SELECT   FROM  results FOR XML PATH  TR    ELEMENTS XSINIL   --convert the way ELEMENTS XSINIL display NULL to display word NULL SET  htmlTD   REPLACE  htmlTD    xsi nil  true   gt      gt NULL lt  TD gt    SET  htmlTD   REPLACE  htmlTD    lt TR xmlns xsi  http   www w3 org 2001 XMLSchema-instance  gt      lt TR gt     --FOR XML PATH will set tags for each column name   lt columnName1 gt abc lt  columnName1 gt  lt columnName2 gt def lt  columnName2 gt  --this will replace all the column names with TD  html table data tag  SELECT  htmlTD   REPLACE REPLACE  htmlTD    lt     name     gt      lt TD gt       lt      name     gt      lt  TD gt    FROM tempdb sys columns WHERE object id   OBJECT ID  tempdb dbo  results     SELECT   lt TABLE cellpadding  2  cellspacing  2  border  1  gt            lt TR gt      htmlTH     lt  TR gt           htmlTD          lt  TABLE gt

User · Answer

Following piece of code  I have prepared for generating the HTML file for documentation which includes Table Name and Purpose in each table and Table Metadata information  It might be helpful   use Your Database Name  print   lt  DOCTYPE html gt   PRINT   lt html gt  lt body gt   SET NOCOUNT ON DECLARE  tableName VARCHAR 30  DECLARE tableCursor CURSOR LOCAL FAST FORWARD FOR     SELECT T name AS TableName        FROM sys objects AS T      WHERE T type desc    USER TABLE       ORDER BY T name OPEN tableCursor FETCH NEXT FROM tableCursor INTO  tableName WHILE   FETCH STATUS   0 BEGIN     print   lt table gt       print   lt tr gt  lt td gt  lt b gt Table Name   lt b gt  lt  td gt  lt td gt    tableName   lt  td gt  lt  tr gt       print   lt tr gt  lt td gt  lt b gt Prupose   lt b gt  lt  td gt  lt td gt     YOu can Fill later     lt  td gt  lt  tr gt       print   lt  table gt        print   lt table gt       print   lt tr gt  lt th gt ColumnName lt  th gt  lt th gt DataType lt  th gt  lt th gt Size lt  th gt  lt th gt PrecScale lt  th gt  lt th gt Nullable lt  th gt  lt th gt Default lt  th gt  lt th gt Identity lt  th gt  lt th gt Remarks lt  th gt  lt  tr gt       SELECT  concat   lt tr gt  lt td gt                LEFT C name  30    AS ColumnName     lt  td gt  lt td gt               LEFT ISC DATA TYPE  10    AS DataType     lt  td gt  lt td gt               C max length   AS Size     lt  td gt  lt td gt               CAST P precision AS VARCHAR 4           CAST P scale AS VARCHAR 4     AS PrecScale     lt  td gt  lt td gt               CASE WHEN C is nullable   1 THEN  Null  ELSE  No Null  END   AS  Nullable      lt  td gt  lt td gt               LEFT ISNULL ISC COLUMN DEFAULT        5     AS  Default      lt  td gt  lt td gt               CASE WHEN C is identity   1 THEN  Identity  ELSE    END   AS  Identity      lt  td gt  lt td gt  lt  td gt  lt  tr gt        FROM   sys objects AS T            JOIN sys columns AS C ON T object id   C object id            JOIN sys types AS P ON C system type id   P system type id and c user type id   p user type id            JOIN INFORMATION SCHEMA COLUMNS AS ISC ON T name   ISC TABLE NAME AND C name   ISC COLUMN NAME     WHERE  T type desc    USER TABLE        AND  T name    tableName     ORDER BY T name  ISC ORDINAL POSITION     print   lt  table gt       print   lt  br gt       FETCH NEXT FROM tableCursor INTO  tableName  END  CLOSE tableCursor DEALLOCATE tableCursor SET NOCOUNT OFF PRINT   lt  body gt  lt  html gt

[html] Convert a SQL query result table to an HTML table for email

Examples related to html

Examples related to sql

Examples related to sp-send-dbmail