24 October 2008 09:13 tonyrogerson

Format query output into an HTML table - the easy way

Ok, I wrote a big stored procedure last time my entry on "send table or view as embedded html"; I've found a much simpler method in pure SQL....

 

declare @body varchar(max)

 

--    Create the body

set @body = cast( (

select td = dbtable + '</td><td>' + cast( entities as varchar(30) ) + '</td><td>' + 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 = '<table cellpadding="2" cellspacing="2" border="1">'

              + '<tr><th>Database Table</th><th>Entity Count</th><th>Total Rows</th></tr>'

              + replace( replace( @body, '&lt;', '<' ), '&gt;', '>' )

              + '<table>'

 

 

print @body

 

Then just use whatever email mechanism you want, for instance my CLR proc: http://sqlblogcasts.com/blogs/tonyrogerson/archive/2007/12/31/clr-stored-procedure-to-utilise-more-of-the-mailmessage-net-class-rather-than-db-mail.aspx

 

Filed under: , ,

Comments

# re: Format query output into an HTML table - the easy way

24 October 2008 12:09 by Madhivanan

Very nice

# Biggest Halloween &raquo; Blog Archive &raquo; Format query output into an HTML table - the easy way

Pingback from  Biggest Halloween  &raquo; Blog Archive   &raquo; Format query output into an HTML table - the easy way

# Creating an HTML table from SQL

26 October 2008 23:03 by SimonS Blog on SQL Server Stuff

There used to be a stored proc you could use to create an HTML table from a query. The only time I came

# re: Format query output into an HTML table - the easy way

05 November 2008 14:24 by Prabha83

Hi,

I want to change color of the cell based on the value of a partcular column.

For Ex :

I have two column like Name & Status

If value in Status is 'Lock', then the cell color should be red

If value in status is 'Unlock' then the cell color should be Green

Please give me a solution.

Thanks,

Prabha

# re: Format query output into an HTML table - the easy way

05 November 2008 14:33 by tonyrogerson

Hi Prabha,

Not too difficult at all and introduces some of the power of the CASE expression.

Modify the query....

select td = dbtable + '</td><td' + case when rows > 11 then ' style="background-color=red"' else '' end + '>' + cast( entities as varchar(30) ) + '</td><td>' + cast( rows as varchar(30) )

Where the CASE is your logic and the style is for creating a style on the HTML table.

Tony.

# Websites tagged "max" on Postsaver

28 April 2009 16:17 by Websites tagged "max" on Postsaver

Pingback from  Websites tagged "max" on Postsaver

# re: Format query output into an HTML table - the easy way

08 February 2010 12:47 by redbeard

hi i have one table with 7 fields two of the fields have a username and a text hyperlink value i am attempting to output to either html or php and the user name become an hyperlink the links are required for gaming gangs to enable them to attach each other... is this possible?

redbeard

# re: Format query output into an HTML table - the easy way

08 February 2010 12:58 by redbeard

sorry my last comment was in wrong place

# How to generate a HTML table using FOR XML PATH | LouieBao.Net

Pingback from  How to generate a HTML table using FOR XML PATH | LouieBao.Net

# Format query output into an HTML table - the ea...

Pingback from  Format query output into an HTML table - the ea...