SQLTableToHTMLTable  

Posted by ReelTym


if object_id( 'SQLTableToHTMLTable' ) is null
exec ( 'create proc SQLTableToHTMLTable as return null ' )
go

alter proc SQLTableToHTMLTable
( @pTableName sysname, @pOrderBy nvarchar(4000), @pHTML varchar(max) OUTPUT )
as
begin
-- Grab the header row html and the sql for selecting the data rows
declare @header varchar(max), @data varchar(max)
select
@header = '~END~', @data = '~END~'
select
@header = name + '</th><th>' + @header,
@data = '
isnull(convert(varchar(max),' + name + case when c.user_type_id in ( 58, 61 ) then ',121' else '' end + '),'' '') + ''</td><td>'' + ' + @data
from (
select * from tempdb.sys.columns where object_id = object_id( @pTableName )
union all
select * from sys.columns where object_id = object_id( @pTableName )
) c
order by column_id desc
select
@header = '<tr><th>' + replace( @header, '</th><th>~END~', '' ) + '</th></tr>',
@data = replace( @data, ' + ''</td><td>'' + ~END~', '' )

-- Grab the data row html
declare @sql nvarchar(4000)
select @sql = '
set @htmlOUT
= convert(
varchar(max),
(
select
td = ' + @data + '
from ' + @pTableName + isnull( '
order by ' + @pOrderBy, '' ) + '
for xml path( ''tr'' ), type
)
)
'
exec sp_executesql @sql, N'@htmlOUT varchar(max) OUTPUT', @htmlOUT = @pHTML OUTPUT

-- Place header and data rows in the table
set @pHTML
= '
<table cellpadding="2" cellspacing="2" border="1">
' + @header + '
' + replace( replace( replace( @pHTML, '&lt;', '<' ), '&gt;', '>' ), '</tr><tr>', '</tr>
<tr>' ) + '
</table>
'
end
go

---------------------------------
-- Example: Generate html table from a temp table

-- Step 1: Make sure the temp table doesn't exist yet
if object_id( 'tempdb..#temp' ) is not null
drop table #temp

-- Step 2: Create temp table
select
dbtable = object_name( object_id ),
*
into #temp
from sys.columns c

-- Step 3: Generate an html table from the temp table
declare @html varchar(max)
exec dbo.SQLTableToHTMLTable @pTableName = 'tempdb..#temp', @pOrderBy = 'object_id, column_id', @pHTML = @html output

-- Step 4: Render results as xml so we can view all the html
select html = convert(xml,@html)
go

---------------------------------
-- Example: Generate html table from a regular table

-- Step 1: Make sure the regular table doesn't exist yet
if object_id( 'TestTable' ) is not null
drop table TestTable

-- Step 2: Create regular table
select
dbtable = object_name( object_id ),
*
into TestTable
from sys.columns c

-- Step 3: Generate an html table from the regular table
declare @html varchar(max)
exec dbo.SQLTableToHTMLTable @pTableName = 'TestTable', @pOrderBy = 'object_id, column_id', @pHTML = @html output

-- Step 4: Render results as xml so we can view all the html
select html = convert(xml,@html)
go

Tony Rogerson - Format Query Output into an HTML Table the-easy-way  

Posted by ReelTym


declare @body varchar(max)

-- Create the body
set @body
= convert(
varchar(max),
(
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
)
)

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( replace( @body, '&lt;', '<' ), '&gt;', '>' ), '</tr><tr>', '</tr>
<tr>' ) + '
</table>
'

print @body
select body = convert( xml, @body )

Lee Everest's SQL Server Weblog  

Posted by ReelTym

Sharing tips and tricks so that you might avoid the beatings that I've endured.