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