SQLS*Plus for SQL Server: HTML Data Output

4 min readJul 17, 2020

SQLS*Plus for SQL Server Data Output — HTML Data Output, CSV Data Output, JSON Data Output, Vertical Data Output and Column Autoformatting.

HTML DATA OUTPUT

Use “set markup html on|off” command to output data in HTML format

Sample SQL script for HTML output:

D:\sqlsplus>type t2.sql

set pages 10

ttitle LEFT 'this is a top title'

set markup html on
spool xx.htm

select top 25 name n1, id, name n2 from sysobjects;

spool off

set markup html off

host xx.htm

HTML output:

CSV DATA OUTPUT

Use “set output csv” command to output data in CSV format.

Sample SQL script for CSV output:

0:sa@192.168.1.160> set output csv
0:sa@192.168.1.160> set head off
0:sa@192.168.1.160> set pages 0

CSV output:

0:sa@192.168.1.160> select name,crdate from sys.sysobjects;
"sysrowsetcolumns","2005-10-14 01:36:15.923",
"sysrowsets","2005-10-14 01:36:15.910",
"sysallocunits","2005-10-14 01:36:15.910",
"sysfiles1","2003-04-08 09:13:38.093",
"syshobtcolumns","2005-10-14 01:36:15.940",
"syshobts","2005-10-14 01:36:15.923",
"sysftinds","2005-10-14 01:36:17.063",
"sysserefs","2005-10-14 01:36:15.940",
"sysowners","2005-10-14 01:36:17.050",
"sysprivs","2005-10-14 01:36:15.877",
"sysschobjs","2005-10-14 01:36:15.987",
…

JSON DATA OUTPUT

Use “set output json” command to output data in JSON format.

Sample SQL script for JSON output:

0:sa@192.168.1.160> set output json
0:sa@192.168.1.160> set head off
0:sa@192.168.1.160> set pages 0

JSON output:

0:sa@192.168.1.160\SQLSERVER2008> select * from department order by dept_id;
{ "dept_id" : "10", "last_name" : "Jackson", "salary" : "50000", "bonus" : "12501.78" }
{ "dept_id" : "10", "last_name" : "Sally", "salary" : "55000", "bonus" : "13750" }
{ "dept_id" : "10", "last_name" : "Major", "salary" : "30000", "bonus" : "7500" }
{ "dept_id" : "10", "last_name" : "Mimon", "salary" : "38000", "bonus" : "9500" }
{ "dept_id" : "10", "last_name" : "Karla", "salary" : "58000", "bonus" : "14500" }
{ "dept_id" : "10", "last_name" : "Major", "salary" : "34000", "bonus" : "8500" }
{ "dept_id" : "10", "last_name" : "Mason", "salary" : "39000", "bonus" : "9750" }
{ "dept_id" : "10", "last_name" : "Mason", "salary" : "39000", "bonus" : "9750" }
{ "dept_id" : "10", "last_name" : "Jackson", "salary" : "50000", "bonus" : "12501.78" }
{ "dept_id" : "10", "last_name" : "Sally", "salary" : "55000", "bonus" : "13750" }
{ "dept_id" : "10", "last_name" : "Major", "salary" : "30000", "bonus" : "7500" }
{ "dept_id" : "10", "last_name" : "Mimon", "salary" : "38000", "bonus" : "9500" }
{ "dept_id" : "10", "last_name" : "Karla", "salary" : "58000", "bonus" : "14500" }
{ "dept_id" : "10", "last_name" : "Major", "salary" : "34000", "bonus" : "8500" }
{ "dept_id" : "10", "last_name" : "Mason", "salary" : "39000", "bonus" : "9750" }
{ "dept_id" : "10", "last_name" : "Mason", "salary" : "39000", "bonus" : "9750" }
{ "dept_id" : "20", "last_name" : "Smith", "salary" : "45000", "bonus" : "23000" }
{ "dept_id" : "20", "last_name" : "<NULL>", "salary" : "65000", "bonus" : "29000" }
{ "dept_id" : "20", "last_name" : "Major", "salary" : "78000", "bonus" : "" }
{ "dept_id" : "20", "last_name" : "Smith", "salary" : "75000", "bonus" : "18750" }
{ "dept_id" : "20", "last_name" : "Jefferson", "salary" : "90000", "bonus" : "22500" }
{ "dept_id" : "20", "last_name" : "Smith", "salary" : "45000", "bonus" : "23000" }
{ "dept_id" : "20", "last_name" : "<NULL>", "salary" : "65000", "bonus" : "29000" }
{ "dept_id" : "20", "last_name" : "Major", "salary" : "78000", "bonus" : "" }
{ "dept_id" : "20", "last_name" : "Smith", "salary" : "75000", "bonus" : "18750" }
{ "dept_id" : "20", "last_name" : "Jefferson", "salary" : "90000", "bonus" : "22500" }
{ "dept_id" : "30", "last_name" : "Sandy Jackson", "salary" : "38000", "bonus" : "19000" }
.…

VERTICAL DATA OUTPUT

Use “set vout on” command to output data in vertical format, where each column is printed on its own line. Vertical output format is helpful when outputting data from a tables with many columns.

Sample SQL script for CSV output:

0:sa@192.168.1.160> set vout on

Vertical output:

0:sa@192.168.1.160> select name,crdate from sys.sysobjects;

name | sysrowsetcolumns
crdate| 2005-10-14 01:36:15.923

name | sysrowsets
crdate| 2005-10-14 01:36:15.910

name | sysallocunits
crdate| 2005-10-14 01:36:15.910

name | sysfiles1
crdate| 2003-04-08 09:13:38.093
…

COLUMN AUTOFORMATTING

Use “set autoformat <table>” command to automatically format table columns to optimally size column sizes for character and numeric fields.

set autoformat supports 2 parameters:

maxsize — defined maximum size for long character columns, default is 40 characters sample — defined sample size for table data selection to identify optimal columns sizes, default is 5%.

Sometime default sample is not enough and for small table recommendation is to set sample to 50%-100%.

Sample table columns autoformatting:

0:sa@192.168.1.160\SQLSERVER2008> set autoformat SalesLT.Customer

Unable to create automatic column formatting. Please increase sample size and retry:

0:sa@192.168.1.160\SQLSERVER2008> set autoformat SalesLT.Customer sample 50
0:sa@192.168.1.160\SQLSERVER2008> col
COLUMN MIDDLENAME
FORMAT A10

COLUMN CUSTOMERID
FORMAT 9999999999

COLUMN PASSWORDHASH
FORMAT A40

COLUMN SALESPERSON
FORMAT A24

COLUMN COMPANYNAME
FORMAT A36

COLUMN PASSWORDSALT
FORMAT A12

COLUMN TITLE
FORMAT A5

COLUMN LASTNAME
FORMAT A22

COLUMN FIRSTNAME
FORMAT A15

COLUMN SUFFIX
FORMAT A6

COLUMN EMAILADDRESS
FORMAT A34

COLUMN PHONE
FORMAT A19