SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

Get your favorite SSC scripts directly in SSMS with the free SQL Scripts addin. Search for scripts directly from SSMS, and instantly access any saved scripts in your SSC briefcase from the favorites tab.
Download now (direct download link)

SQL table to HTML

By Eduardo Pivaral,

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:

table 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:

table 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:

table 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:

table 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:

table 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:

table 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:

table output

Total article views: 725 | Views in the last 30 days: 43
 
Related Articles
FORUM

Not Selecting All Columns in select query

Not Selecting All Columns in select query

FORUM

How to Specify Length of Output Column in SELECT Stmt?

How to Specify Length of Output Column in SELECT Stmt?

FORUM

INSERT OUTPUT INTO (With an extra column) and SELECT statement

I need to select a column for the OUTPUT INTO from the SELECT table for the INSERT...

FORUM

Help me: QUERY OUTPUT

Help me: QUERY OUTPUT

FORUM

Select query output to a table

Output my query reult to a table

Tags
html    
t-sql    
 
Contribute