Standard

Posted by

Posted on

March 14, 2021

Posted under

Tsql

Comments

Leave a comment

Create HTML from TSQL / SQL Server Queries

  1. Below code can be used to create HTML output as a file from TSQL / SQL Server queries,
/*
Script         : TSQL_Using_HtmlFile.sql
Version        : 1.0
Date           : 14/03/2021
Tested         : SQL server 2008 and above
Requirement    : User running the script should be a SYSADMIN.
		 sp_configure 'xp_cmdshell' and 'Ole Automation Procedures' should be enabled.
Errors or BUGS : Please send an email to Vivek Janakiraman (jvivek2k1@yahoo.com)
Author         : Vivek Janakiraman
*/
use tempdb
go
Set NoCount On;
SET concat_null_yields_null OFF;

Declare @Body nvarchar(max)
,  @TableHead varchar(max)
,  @TableTail varchar(max)

Declare @file nvarchar(max)
Declare @filelocation nvarchar(max)
Declare @filename nvarchar(max)
Declare @Final_filename nvarchar(max)
Declare @Source nvarchar(max)
DECLARE @OLE INT
DECLARE @FileID INT
DECLARE @file_temp varchar(max)
exec xp_cmdshell 'md c:\temp'
set @filelocation = 'c:\temp\'
set @Final_filename = @filelocation + 'counter.txt'


Set @TableTail = '</table></body></html>';
Set @TableHead = '<html><head>' +
'<style>' +
'td {align=center;padding-left:5px;padding-right:5px;padding-top:3px;padding-bottom:3px;font-size:11pt;} ' +
'</style>' +
'</head>' +
'<body><table cellpadding=0 cellspacing=0 border=0 align=center WIDTH=1700>' +
'<tr bgcolor=C6CFFF><td align=center><b>SQL REPORT</b></td>' +
'<tr><td align=center></td>' +
'<tr><td align=center></td>' +
'<tr><td align=center></td>' +
'<tr><td align=center><u><b>Object Details:</b></u></td>' +
'<tr><td align=center></td>' +
'<tr><td align=center></td>' +
'<tr><td align=center></td>' +
'</tr></table>';

Select @Body = '<table style=\"font-family: Calibri; font-size:85%; \" ALIGN=center BORDER=1 CELLPADDING=1 CELLSPACING=1 WIDTH=1700 WIDTH=1700>
<tr bgcolor=FFEFD8>
      <td><b>ObjectName</b></td>
      <td><b>type_desc</b></td>
</tr>'

Select @Body = @Body + (select top 10
name As [TD]
,type_desc As [TD] 
from sys.objects 
For XML raw('tr'), Elements)

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
set @Final_filename = @filelocation + 'Vivek.html'
EXECUTE sp_OACreate 'Scripting.FileSystemObject', @OLE OUT
EXECUTE sp_OAMethod @OLE, 'OpenTextFile', @FileID OUT, @Final_filename, 8, 1
EXECUTE sp_OAMethod @FileID, 'WriteLine', Null, @Body
EXECUTE sp_OADestroy @FileID
EXECUTE sp_OADestroy @OLE

Below file can be viewed from location C:\temp.

2. Below code can be used to create HTML output as an email from TSQL / SQL Server queries,

/*
Script         : TSQL_Using_Html_Email.sql
Version        : 1.0
Date           : 14/03/2021
Tested         : SQL server 2008 and above
Requirement    : User running the script should be a SYSADMIN.				 
Errors or BUGS : Please send an email to Vivek Janakiraman (jvivek2k1@yahoo.com)
Author         : Vivek Janakiraman
*/
use tempdb
go
Set NoCount On;
SET concat_null_yields_null OFF;

Declare @Body nvarchar(max)
,  @TableHead varchar(max)
,  @TableTail varchar(max)

Set @TableTail = '</table></body></html>';
Set @TableHead = '<html><head>' +
'<style>' +
'td {align=center;padding-left:5px;padding-right:5px;padding-top:3px;padding-bottom:3px;font-size:11pt;} ' +
'</style>' +
'</head>' +
'<body><table cellpadding=0 cellspacing=0 border=0 align=center WIDTH=1700>' +
'<tr bgcolor=C6CFFF><td align=center><b>SQL REPORT</b></td>' +
'<tr><td align=center></td>' +
'<tr><td align=center></td>' +
'<tr><td align=center></td>' +
'<tr><td align=center><u><b>Object Details:</b></u></td>' +
'<tr><td align=center></td>' +
'<tr><td align=center></td>' +
'<tr><td align=center></td>' +
'</tr></table>';

Select @Body = '<table style=\"font-family: Calibri; font-size:85%; \" ALIGN=center BORDER=1 CELLPADDING=1 CELLSPACING=1 WIDTH=1700 WIDTH=1700>
<tr bgcolor=FFEFD8>
      <td><b>ObjectName</b></td>
      <td><b>type_desc</b></td>
</tr>'

Select @Body = @Body + (select top 10
name As [TD]
,type_desc As [TD] 
from sys.objects 
For XML raw('tr'), Elements)

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

EXEC msdb.dbo.sp_send_dbmail @body = @Body
,@body_format = 'HTML'
,@profile_name = N'DBMAIL'
,@recipients = N'jvivek2k1@yahoo.com'
,@Subject = N'Object Details'

Thank You,
Vivek Janakiraman

Disclaimer:
The views expressed on this blog are mine alone and do not reflect the views of my company or anyone else. All postings on this blog are provided “AS IS” with no warranties, and confers no rights.

Leave a Reply