Home » Wrapping a table into Html Table in SQL Server 2005-2008

Wrapping a table into Html Table in SQL Server 2005-2008

Wrapping a table into Html Table in SQL Server 2005-2008:

Say you like to wrap the query to check last back dates on a SQL instance into a html table below:

select name, convert(varchar(10),create_date,103) as createddate ,recovery_model,compatibility_level from sys.databases


To Wrap it into a HTML use the below code:

Declare @Body varchar(max),

@TableHead varchar(max),

@TableTail varchar(max)

Set NoCount On;

Set @TableTail = '</table></body></html>';

Set @TableHead = '<html><head>' +

'<style>' +

'td {border: solid black 1px;padding-left:5px;padding-right:5px;padding-top:1px;padding-bottom:1px;font-size:11pt;} ' +

'</style>' +

'</head>' +

'<body><table cellpadding=0 cellspacing=0 border=0>' +

'<tr bgcolor=#FFEFD8><td align=center><b>Database Name</b></td>' +

'<td align=center><b>Create Date</b></td>' +

'<td align=center><b> Recovery Model Date</b></td>' +

'<td align=center><b> compatibility level</b></td>';

Select @Body = ( SELECT Row_Number() Over(order by name) % 2 As [TRRow],
Name As [TD],
convert(varchar(10),create_date,103) as [TD align=center],
recovery_model as [TD align=center],
compatibility_level as [TD align=center]
from sys.databases order by name

For XML raw('tr'), Elements)

-- Replace the entity codes and row numbers

Set @Body = Replace(@Body, '_x0020_', space(1))

Set @Body = Replace(@Body, '_x003D_', '=')

Set @Body = Replace(@Body, '<tr><TRRow>1</TRRow>', '<tr bgcolor=#C6CFFF>')

Set @Body = Replace(@Body, '<TRRow>0</TRRow>', '')

Select @Body = @TableHead + @Body + @TableTail

-- return output
Select @Body

---For Sending it as email use SP_Send_DBMail as below

EXEC MSDB.dbo.Sp_Send_Dbmail
@Profile_Name = 'Demo',
@Recipients = '[email protected]',
@Body = @Body,
@body_format='html',
@subject='Backup check-html',
@attach_query_result_as_file=0

Leave a Reply

Your email address will not be published. Required fields are marked *