http://www.sqlservercentral.com/scripts/TSQL/175511/?utm_source=SSC&utm_medium=pubemail

SQL table to HTML

By Eduardo Pivaral, 2018/08/09

Convert any table or select query to a html table format

FROM: www.sqlguatemala.com

SP: sp_TabletoHTML

Version: 1.1

AUTHOR: Eduardo Pivaral sqlguatemala.com

MIT License

This stored procedure converts a table or select query to a HTML table format, with some customization options.

I have taken as a base, a script Carlos Robles (dbamastery.com) provided me for a static table, so i modified it to accept any table and apply different or no styles, also you can output or not the column names to the table.

NOTES:

This SP works with dynamic queries, also data is not validated, so it is vulnerable to SQL injection attacks, so always validate your queries first.
Null values are not converted on this initial release, so before using it, remove null values from your data.
Some special datatypes like geography, timestamp, xml, image are not supported, if you try to use them, an error will raise, remove these columns before using it.
This tool is not designed to handle huge amounts of data, so, for massive information you can split them in various executions.

PARAMETERS:

@stTable: input table or SELECT query, a schema.object or SELECT query format
@RawTableStyle: OUTPUT variable, to use in another process or programatically
@includeColumnName: 0=does not include column names | 1=include column names (DEFAULT)
@TableStyle: 0=no style | 1=black borders (DEFAULT) | 2=grey style | 3=lightblue style | 4=zebra-striped table

SAMPLE EXECUTION:

Most basic usage, table name and all defaults to query window

EXEC sp_TabletoHTML @stTable = 'sys.dm_os_windows_info'

Output:

SELECT QUERY, all defaults

SET @SQLSentence = 'SELECT name,state_desc ,create_date,collation_name FROM sys.databases'

EXEC sp_TabletoHTML @stTable = @SQLSentence,
 @RawTableStyle = @st OUTPUT

Output:

Remove column name

SET @SQLSentence = 'SELECT name,state_desc ,create_date,collation_name FROM sys.databases'

EXEC sp_TabletoHTML @stTable = @SQLSentence,
 @includeColumnName = 0,
 @RawTableStyle = @st OUTPUT

Output:

Gray style with columns

SET @SQLSentence = 'SELECT name,state_desc ,create_date,collation_name FROM sys.databases'

EXEC sp_TabletoHTML @stTable = @SQLSentence,
 @TableStyle = 2,
 @RawTableStyle = @st OUTPUT

Output:

Lightblue with columns

SET @SQLSentence = 'SELECT name,state_desc ,create_date,collation_name FROM sys.databases'

EXEC sp_TabletoHTML @stTable = @SQLSentence,
 @TableStyle = 3,
 @RawTableStyle = @st OUTPUT

Output:

zebra-striped table

SET @SQLSentence = 'SELECT name,state_desc ,create_date,collation_name FROM sys.databases'

EXEC sp_TabletoHTML @stTable = @SQLSentence,
 @TableStyle = 4,
 @RawTableStyle = @st OUTPUT

Output:

Remove style and columns

SET @SQLSentence = 'SELECT name,state_desc ,create_date,collation_name FROM sys.databases'

EXEC sp_TabletoHTML @stTable = @SQLSentence,
 @TableStyle = 0,
 @includeColumnName = 0,
 @RawTableStyle = @st OUTPUT

Output:

IF OBJECT_ID('dbo.sp_TabletoHTML') IS NULL

EXEC ('CREATE PROCEDURE dbo.sp_TabletoHTML AS SELECT 1;');

ALTER PROCEDURE [dbo].[sp_TabletoHTML]

@stTable AS NVARCHAR(max),

@RawTableStyle AS NVARCHAR(max) = '' OUTPUT,

@includeColumnName AS BIT = 1,

@TableStyle AS TINYINT = 1

BEGIN

/*******************************************************************************

Convert any table or select query to a html <table> format

FROM: https://github.com/Epivaral/sql-table-to-html

SP: sp_TabletoHTML

Version: 1.1

AUTHOR: Eduardo Pivaral (www.sqlguatemala.com)

MIT License

This stored procedure converts a table or select query to a HTML table format,

with some customization options.

I have taken as a base, a script Carlos Robles (http://dbamastery.com/)

provided me for a static table, so i modified it to accept any table and apply different

or no styles, also you can output or not the column names to the table.

NOTES:

----------

* This SP works with dynamic queries, also data is not validated,

so it is vulnerable to SQL injection attacks, so always validate your queries first.

* Null values are not converted on this initial release, so before using it,

remove null values from your data.

* Some special datatypes like geography, timestamp, xml, image are not supported,

if you try to use them, an error will raise, remove these columns before using it.

* This tool is not designed to handle huge amounts of data, so, for massive information

you can split them in various executions.

-----------

PARAMETERS:

-----------

@stTable: input table or SELECT query, a schema.object or SELECT query format

@RawTableStyle: OUTPUT variable, to use in another process or programatically

@includeColumnName: 0=does not include column names | 1=include column names (DEFAULT)

@TableStyle: 0=no style | 1=black borders (DEFAULT) | 2=grey style | 3=lightblue style | 4=zebra-striped table

-----------

SAMPLE EXECUTION:

-----------------

Most basic execution with all defaults:

EXEC sp_TabletoHTML @stTable = 'sys.dm_os_sys_info'

more execution examples at: https://github.com/Epivaral/sql-table-to-html

-----------------

*******************************************************************************/

SET NOCOUNT ON;

IF EXISTS (

SELECT *

FROM tempdb.INFORMATION_SCHEMA.TABLES

WHERE TABLE_NAME = '##rowstablePreHTML'

)

BEGIN

DROP TABLE ##rowstablePreHTML

END

/*** BASIC SQL Injection detection, always make your own validations before input any code */

IF (

(CHARINDEX('DELETE ', @stTable) > 0)

OR (CHARINDEX('INSERT ', @stTable) > 0)

OR (CHARINDEX('UPDATE ', @stTable) > 0)

OR (CHARINDEX('INTO ', @stTable) > 0)

OR (CHARINDEX('DROP ', @stTable) > 0)

OR (CHARINDEX('TRUNCATE ', @stTable) > 0)

OR (CHARINDEX('MERGE ', @stTable) > 0)

OR (CHARINDEX('EXEC ', @stTable) > 0)

OR (CHARINDEX('EXECUTE ', @stTable) > 0)

OR (CHARINDEX('--', @stTable) > 0)

OR (CHARINDEX(';', @stTable) > 0)

OR (CHARINDEX('XP_', @stTable) > 0)

OR (CHARINDEX('KILL ', @stTable) > 0)

)

BEGIN

THROW 50000,'Only Select statements or schema.object names are allowed as an input',1;

RETURN;

END

/********************************************************************************************/

/************** INTERNAL PARAMETERS **************/

DECLARE @ColumnST AS NVARCHAR(max) = ''

DECLARE @newSelect AS NVARCHAR(max) = ''

DECLARE @ColumnNamesHTML AS NVARCHAR(max) = ''

DECLARE @CSSTableStyle AS NVARCHAR(max) = ''

DECLARE @SelectStatement as NVARCHAR(max) = ''

DECLARE @STErrorMessage as NVARCHAR(max)=''

/************* END INTERNAL PARAMETERS *************/

IF(CHARINDEX('SELECT',@stTable)>0)

BEGIN

SET @SelectStatement = 'SELECT tbl1.* into ##rowstablePreHTML FROM (' + @stTable+') tbl1'

END

ELSE

BEGIN

SET @SelectStatement= 'SELECT tbl1.* into ##rowstablePreHTML FROM ' + @stTable + ' tbl1'

END

BEGIN TRY

EXEC sp_executesql @SelectStatement --Loading table contents on temp table

END TRY

BEGIN CATCH

SET @STErrorMessage ='Syntax related issue with your input, use "<schema>.<table>" or "SELECT <columns> FROM <TABLE>" format. ERROR REPORTED: '+ERROR_MESSAGE();

THROW 50000, @STErrorMessage ,1;

RETURN;

END CATCH;

SET @ColumnST = (

SELECT '[' + COLUMN_NAME + ']' AS 'TH'

FROM tempdb.INFORMATION_SCHEMA.COLUMNS

WHERE TABLE_NAME LIKE '##rowstablePreHTML'

FOR XML raw('TR'),

elements

) -- Obtain columns for the table

-- performing cleanup task for columns

SET @ColumnST = REPLACE(@ColumnST, '<TR>', '')

SET @ColumnST = REPLACE(@ColumnST, '</TR>', '')

SET @ColumnNamesHTML = '<TR>' + @ColumnST + '</TR>' --Obtaining column names, we will use this later to append to the table

-- Removing [] we put before on the columns

SET @ColumnNamesHTML = REPLACE(@ColumnNamesHTML, '[', '')

SET @ColumnNamesHTML = REPLACE(@ColumnNamesHTML, ']', '')

-- we continue cleanup tasks for columns before creating or dynamic Select

SET @ColumnST = REPLACE(@ColumnST, '<TH>', '')

SET @ColumnST = REPLACE(@ColumnST, '</TH>', 'as TD,') --creating all column names with the same name

SET @ColumnST = LEFT(@ColumnST, LEN(@ColumnST) - 1) -- Removing last comma

-- generating select statement on an HTML friendly format

SET @newSelect = 'SELECT @RawTableStyleOUT =(SELECT ' + isnull(@ColumnST, '') + ' FROM ##rowstablePreHTML For XML RAW(''TR''), ELEMENTS)'

EXEC sp_executesql @newSelect,

N' @RawTableStyleOUT as nvarchar(max) OUTPUT',

@RawTableStyleOUT = @RawTableStyle OUTPUT

IF (@TableStyle = 0) -- no Style

BEGIN

SET @CSSTableStyle = ''

END

IF (@TableStyle = 1) -- black borders

BEGIN

SET @CSSTableStyle = '<style type="text/css">

table, th, td {border: 1px solid; border-collapse: collapse;}

</style>'

END

IF (@TableStyle = 2) -- Grey style

BEGIN

SET @CSSTableStyle = '<style type="text/css">

table, td, tr {border: 1px solid #dddddd; padding: 3px; color: #555555; border-collapse: collapse;}

th {background-color: #dddddd;}

</style>'

END

IF (@TableStyle = 3) -- lightblue borders

BEGIN

SET @CSSTableStyle = '<style type="text/css">

table, td, tr {border: 1px solid #DCDCDC; padding: 2px; color: #808080; border-collapse: collapse;}

th {border: 1px solid #DCDCDC;background-color: #1E90FF;color:#FFFFFF;}

</style>'

END

IF (@TableStyle = 4) -- zebra-striped table

BEGIN

SET @CSSTableStyle = '<style type="text/css">

table, td, tr {border: 1px solid #dddddd; padding: 3px; color: #555555; border-collapse: collapse;}

th {background-color: #CCCCCC;}

tr:nth-child(even) {background-color: #F7F7F7}

</style>'

END

IF(@includeColumnName =1) --IF Column names must be included

BEGIN

SET @RawTableStyle = @ColumnNamesHTML + @RawTableStyle

END

SET @RawTableStyle = @CSSTableStyle + '<TABLE>' + isnull(@RawTableStyle,'') + '</TABLE>'

SELECT @RawTableStyle

END

GeneralSQL

Thursday, August 9, 2018

SQL table to HTML

SQL table to HTML

Convert any table or select query to a html table format

NOTES:

PARAMETERS:

SAMPLE EXECUTION:

No comments:

Post a Comment