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 )

This entry was posted on Tuesday, February 09, 2010 and is filed under , . You can leave a response and follow any responses to this entry through the Subscribe to: Post Comments (Atom) .

0 comments