a database documentation tool
for:
MS SQL
Server, MS Analysis Server, MS Report Server,
Oracle, MySQL, PostgreSQL, MS Access,
Sybase
ASE, Sybase SQL Anywhere,
IBM DB2
UDB, IBM Informix IDS,
VistaDB, Polyhedra,
and Raima RDM Server
Microsoft
SQL Server 2000/2005/2008
Microsoft
Analysis Server 2005/2008
Microsoft
Report Server 2005/2008
XML
Comments in Oracle packages
First of all, if you are looking
for a copy of SqlSpec, go to www.elsasoft.org.
If you have questions about SqlSpec, please have a look in the forum here: http://www.elsasoft.org/forum. If you don’t see your question answered
there, post it and it will be answered.
Otherwise, you can send mail to sqlspec@elsasoft.org. I’ll respond to you as quick as I can. Thanks!
If you are reporting a bug,
please attach to your email a SQL script that generates a database that
reproduces the bug. If you can’t do it for privacy reasons, I understand of
course. If you can though, it will help
me find and fix the bug!
SqlSpec is a
tool for generating documentation for any database on the following platforms:
* SqlSpec
may work on versions before these, but it’s untested. Please try it and let us know your results.
Given one or more connection
strings, the tool will generate documentation about all the objects (tables,
views, stored procedures, triggers, indexes, constraints, functions, etc) in
your database.
It will also tell you what the
dependencies are (which tables point to other tables via foreign keys, which
tables are used by which stored procedures, etc.) It will show the code for each trigger, view, user defined function, check constraint, and stored
procedure in the database. In this way,
it’s easy to keep documentation in sync with the actual database, since you can
just run the tool and the documentation gets updated.
If you provide more than one
connection string, you can generate a single chm for
multiple databases. This is useful if
you have a large project involving many databases and you want a single
searchable chm file for all of them.
SqlSpec can
also create data model diagrams containing any
number of tables, views, stored procedures, user defined functions, etc. You specify what objects make up each diagram
in a config file.
Other features include:
Certain
features are only applicable to certain platforms however. For instance, there is no such thing as
extended properties in a MySQL, Access, or Analysis
Services database, so any feature related to extended properties would not be
applicable on those platforms.
This section gives you enough
information to get started using SqlSpec, but doesn’t
explain the more advanced features. See
the other sections of this document for that.
Tip: If you have a database with
a large number of objects (thousands, say) it can take a while for SqlSpec to finish.
When learning some of the more advanced features in SqlSpec,
such as data models and exclusion lists, it’s
useful experiment on a small database first.
That way if you go through a period of trial and error, you don’t have
to wait so long between trials.
SqlSpec is
actually a console application that happens to launch a GUI if you invoke it with
no arguments (that’s why you always see a console pop up if you launch it from
the Windows Shell). Use the /? Flag to
get help about the available flags.
Certain features are only available through the command line interface,
and are not exposed in the GUI.
Because SqlSpec
is a console application, it’s easy for you to integrate SqlSpec
into their processes by calling it from .bat files, scheduled tasks, etc. For example, in SQL Server you could create
an Agent job to invoke it every night and place the documentation on a web
server, so that the documentation for your favorite databases is always up to
date and available to anyone who needs it.
All command line flags are
optional, with the exception that you must pass either a connection string or
path to an xml file previously generated by SqlSpec.
If you
are using the GUI and want to see how you would invoke SqlSpec
from the command line with the settings you have specified in the GUI, you can
click the “generate batch file” button and a .bat file will be created for you
with the command line args you would pass. If you have selected objects to document
using the “exclude objects…” button, a config file
will be generated for you as well.
|
[/a 0|1] |
1 = generate XML only, no documentation. Default is 0. That is, the default is to generate human
readable, nicely formatted documentation.
|
|
[/A] |
Contains a comma-separated list of connection strings to MS
Access databases. SqlSpec supports Access 97/2000/XP/2003. |
|
[/AX] |
Contains a comma-separated list of
paths to xml files generated previously by SqlSpec (for MS Access databases). Use this to generate docs for xml files
that you generated previously using the /A
flag. This feature
is not available in the GUI. |
|
[/b] |
Contains a comma-separated list of paths to XML files generated previously by SqlSpec (for SQL Server 2000 and 2005 databases). Use this to generate docs for XML files that you generated previously using the /c flag. This feature is not available in the GUI. |
|
[/B] |
Contains a comma-separated list of paths to xml files generated previously by SqlSpec (for Analysis Server 2005 databases). Use this to generate docs for xmlfiles that you generated previously using the /C flag. This feature is not available in the GUI. |
|
[/c] |
Contains a colon-separated
list of connection strings to SQL Server 2000 or 2005 databases. |
|
[/C] |
Contains a colon-separated list
of connection strings to Analysis Server 2005 databases. |
|
[/CLC] |
Contains a path to
clc.exe. This is necessary only if you
are documenting Polyhedra
databases and want to have the DDL for tables in the docs. |
|
[/color 0|1] |
Specifies whether or not to
colorize DDL in the documentation. 1
means color it, 0 means don’t. Default is 0. |
|
[/cf
0|1] |
Specifies whether or not to
place comments about an object (such as a table column) next
to the column name, or all the way to the right of the table. Default is 0 (to the right). |
|
[/cs] |
Specifies the charset to use when creating the HTML files. Default is “utf-8” so that
Unicode characters will display properly in the HTML. To display chars such as Å, Ä,
Ö, ö, å, in the contents pane of the chm, use
“iso-8859-1”. Depending on your
system, other charsets will be available. You
can see what charsets are available on your system by
launching the GUI and scrolling through the charset
dropdown. |
|
[/css] |
Specify a path to a .css file that will be used to control the look and feel
of the output
documentation. |
|
[/d] |
Contains a path to a XML config file, used
for specifying data model diagrams or an exclusion list.
|
|
/delim |
Specifies an alternate
delimiter to be used for separating connection strings. By default a
colon is used. You would want to use
this if you needed to use a colon in a
single connection string, for example in a password. You can specify any single
character for the delimiter. |
|
[/D int] |
specifies
an int to be used for datetime formats in the docs generated for SQL Server. Allowed values are here: http://msdn2.microsoft.com/en-us/library/ms187928.aspx.
Default value is 100. |
|
[/DB2] |
contains a colon-separated list of
OLEDB connection strings to DB2 databases.
|
|
[/DB2S] |
Contains
a colon-separated list of schema names to be documented, used only when
documenting DB2 databases. If you use this flag, only objects in the schemas
specified in the list will be documented. |
|
[/DB2U] |
Contains a username passed to db2look.exe
for use in generating DDL for tables, views, procedures, etc. Only used when /DB2 is passed. If you don’t pass /DB2U, then no DDL will show in the
output documentation. |
|
[/DB2P] |
Contains a password passed to
db2look.exe for use in generating DDL for tables, views, procedures, etc. Only used when /DB2 is passed. If you don’t pass /DB2U, then no DDL will show in the
output documentation. |
|
[/DGPATH] |
Contains a path to ddlgen.bat. Only used if you want to see the DDL for
tables for Sybase databases. Generally ddlgen.bat is located here: %SYBASE%\ASEP\bin\ddlgen.bat |
|
[/DGUSER] |
Contains a username ser to pass to
ddlgen.bat. |
|
[/DGPASS] |
Contains a passowrd to pass to
ddlgen.bat |
|
[/DGSERVER] |
Contains a server/port pair to pass to
ddlgen.bat, such as MYSERVER:5000 |
|
[/e] |
Contains a comma-separated list of paths to XML files describing what external objects you want to include in the documentation. See the External Objects section. This feature is not available in the GUI. |
|
[/E
0|1] |
Specifies whether to include “edit” hyperlinks next to all extended property comments for objects in SQL Server database specs. This turns on the “edit extended properties” feature. Default is 0. |
|
[/EP] |
Specifies a url to a page that handles the editing of extended properties. Only used if /E 1 is passed. Default value is “save_description.asp”. |
|
[/h 0|1] |
Specifies whether to preserve formatting when displaying extended properties. 0is the default. This feature is not available in the GUI. |
|
[/hhc] |
Specifies the path to hhc.exe. Normally SqlSpec will find this on its own, but if it can’t for some reason, you can pass it on the command line: SqlSpec seems to have trouble finding hhc.exe on 64 bit machines. Hhc.exe is used by SqlSpec to create compiled help (chm) files. |
|
[/hsn
0|1] |
Specifies whether to hide server names in the contents tree of the CHM or Javascript navigation tree. If 0, root nodes will appear as “SERVER.DatabaseName (Platform)”. If 1, they will appear as “DatabaseName (Platform)”. Default is 0. |
|
[/IFX] |
Contains a colon-separated list of OLEDB connection strings to Informix databases. |
|
[/IFXDBS] |
Contains the path to dbschema.exe on your system. This application is used to generate DDL for tables for Informix databases. If not passed, no DDL for tables will show up in the docs. |
|
[/IFXCMD] |
Contains the path to a .cmd file that defines environment variables used by the Informix server. It is required for dbschema.exe to function properly. If not passed, DDL for tables is not shows in the docs. |
|
[/i
0|1] |
Specifies whether to explicitly list out MS_Description and MS_DiagramPane extended properties in the Extended Properties section of the docs. Default is 0. |
|
[/j] |
Specifies the maximum length in characters that you want to use for labels that appear in the reference and dependency graphs. Default is 24. |
|
[/k] |
Specifies the number of icons per row that appear in the reference and dependency graphs. Default is 4. |
|
[/ld path] |
Contains a path to a directory where a
log file named sqlspec_progress.log will be written. If not passed, the value specified by /o is
used. This feature is not available in
the GUI. If you are generating documentation
directly to a public location, this option is useful because the log file may
contain information which you may not want to make public. |
|
[/my] |
Contains a colon-separated list of connection strings to
MySQL 5.0 or above databases. |
|
[/myx] |
Contains a comma-separated list of paths to xml files generated previously by SqlSpec (for MySQL databases). Use this to generate docs for xml files that you generated previously using the /my flag. |
|
[/n] |
A name for your chm file. Default
is the name of the database, or ‘dbspec’ if
multiple values are passed in the /c or /b flags. |
|
[/o path] |
Contains path to the output
directory. Default is current
directory. |
|
[/O] |
Contains a colon-separated list of OLEDB connection strings to Oracle 9i
or above schemas. |
|
[/OS] |
Contains
a colon-separated list of Oracle schema names to be documented, used only
when documenting Oracle schemas. If you use this flag, you should pass the
same number of values as connection strings passed in the /O flag. You can use /OS to document schemas other
than the default schema of the user specified in the connection strings
passed in the /O flag. See the
examples below for how this is used. |
|
[/OX] |
contains a comma-separated list of
paths to xml files generated previously by SqlSpec (for Oracle 9i or 10g
databases). Use this to generate docs
for xml files that you generated previously using the /O flag. |
|
[/pt
0|1] |
Pass 1 to preserve HTML tags in
extended properties/comments. You can
use this if you have, for example, hyperlinks that you want rendered as links
in your documentation. Note that if
you pass 1 here, all your comments MUST be well-formed in the XML sense,
otherwise you will see errors. Default
is 0. |
|
[/PG] |
Contains a colon-separated list of ODBC connection
strings to PostgreSQL 8.0 or above databases. |
|
[/PGDUMP] |
Specifies the path to pg_dump.exe,
which is used to collect DDL for objects in a PostgreSQL
database. Only used if /PG is passed. |
|
[/POLY] |
Specifies a list of OLEDB
connection strings to Polyhedra databases to
document. |
|
[/q] |
1 = quiet mode. 0 is default. This feature is not available in the GUI. |
|
[/r 0|1] |
1 = draw pie charts to show
table sizes, etc. 0 = don’t draw
them. 1 is the default. If you pass 0, this information is shown in
tabular form instead of a pie chart. |
|
[/R int] |
Use this value to specify a
number of sample rows from each table and view that you would like to show in
the generated docs. That is, if you
pass /R 10, then the top 10 rows from every table and view will appear in the
docs. The default value is 0 (no sample
rows). |
|
[/RDM] |
Specifies a list of ODBC
connection strings to Raima RDM Server databases to
document. |
|
[/RDMDDL] |
Specifies the path to
ddlgen.exe. This is a program that
ships with RDM Server that is used to generate DDL. You must pass this if you want to see the
DDL for tables, indexes, and joins in your RDM Server docs. |
|
[/RDMDDLU] |
Specifies the username passed
to ddlgen.exe. Required if you pass
/RDMDDL. |
|
[/RDMDDLP] |
Specifies the password passed
to ddlgen.exe. Required if you pass
/RDMDDL. |
|
[/rn] |
Specifies a name for the root
node in the JavaScript treeview. Default value is “Database Documentation” |
|
[/rs] |
Specifies a list of URLs to
Microsoft Report Server (SSRS) instances to document, separated by commas. |
|
[/rsu] |
Specifies the username to use
when connecting to SSRS instances. If
not specified, SqlSpec will connect to SSRS using
the windows credentials that SqlSpec.exe is running with. Note that SSRS must be configured to allow
Basic Authentication when using this option. |
|
[/rsp] |
Specifies the password to use
when connecting to SSRS instances. If
not specified, SqlSpec will connect to SSRS using
the windows credentials that SqlSpec.exe is running with. Note that SSRS must
be configured to allow Basic Authentication when using this option. |
|
[/rsd] |
Specifies the windows domain
to use when connecting to SSRS instances.
To be used when passing /rsu and /rsp if specifying a domain account. If the user is not a domain users, don’t
pass this arg. |
|
[/snc] |
Specifies the selected node
color in the JavaScript treeview. Default value is “gray”. Other possible values are blue, green, red,
etc. |
|
[/sdp
0|1] |
sdp stands for “show database
properties”. If you pass 0, they will
not be shown. |
|
[/ssp
0|1] |
ssp stands for “show server
properties”. If you pass 0, they will
not be shown. |
|
[/sn
0|1] |
Specifies whether to use short
one-part names in the documentation instead of longer two- and three- part
names. Default is 0. If 1, then a table named ‘[dbo].[authors]’ would appear as
‘authors’ when it is referenced in the documentation. This can lead to some ambiguity if you have
objects with the same name in different schemas. On the other hand the one-part names are
more readable. |
|
[/S int] |
Use this value to specify the
maximum length of characters for sample row data. This arg is only
used if /R is passed with a positive value.
The default value is 100. |
|
[/SY] |
Contains a colon-separated list of AseClient
connection strings to Sybase ASE databases.
|
|
[/stat 0|1] |
1 = generate documentation about table statistics (SQL
Server only). Default is 0. |
|
[/svg
0|1] |
1 = generate SVG diagrams for the foreign key and
dependency graphs. This is useful if
you want to support browsers like Firefox, Opera, Safari, etc. |
|
[/vml
0|1] |
1 = generate VML diagrams. VML is only supported by Internet Explorer and
the CHM viewer. If you want to support
rendering diagrams in all browsers, pass 1 for both /vml
and /svg. |
|
[/t int] |
Used to specify a timeout in
seconds for the SQL queries sent to the servers in the connection strings
passed in /c. 60 seconds is the
default. |
|
[/T 0|1] |
Used to specify if you want
dependencies and pk/fk relationships in tabular
form instead of a graph rendered in VML.
Default is 0 (VML). Pass 1 for
tabular. |
|
[/threads int] |
Specifies the number of worker
threads to use when transforming XML.
If you have multiple processors on the machine where SqlSpec runs, you can put them to use by specifying an value here larger than 1. Default is 1. |
|
[/u 0|1] |
1 = document SQL Server Agent
Jobs, 0 = don’t. Default is 0. |
|
[/VDB] |
Contains a list of connection
strings to VistaDB databases to document. See the VistaDB section below for an example. |
|
[/w 0|1] |
1 = omit system objects, 0 =
don’t. Default is 1. This is useful if
you want to document a database like msdb in SQL
Server, where all the objects are shipped by Microsoft. |
|
[/wn
int] |
Specifies a positive integer
for a number of hours. Any object that
has changed between when you run SqlSpec and this
many hours ago will be shown on a “what’s new” page. It’s a good way to see what’s recently
changed in your database. This feature
is available for these platforms: SQL Server, Oracle, Access, DB2, MySQL, Sybase ASE, Sybase SQL
Anywhere. Default value is 0 so the
page won’t show. |
|
[/x 0|1] |
1 = parse xml comments. Default is 0. |
|
[/X 0|1] |
1 = delete all output files
except for the CHM and sqlspec_progress.log. Default is 0. |
|
[/y 0|1] |
1 = document object
permissions. Default is 0. |
|
[/?] |
Show help on the command line
arguments. |
|
|
|
SqlSpec
supports all major DBMS platforms. The
sections below explain how to generate documentation for each of them using the
command line interface. For more
examples of connection strings to these various platforms, see http://www.connectionstrings.com.
Here are some examples for
invoking SqlSpec against SQL Server databases:
Example:
SqlSpec.exe
/c "server=myserver; database=pubs; user id=myuser; pwd=mypass"
/o "c:\my db specs" /x 1 /t 120 /q 1
In this example, the output would be placed in c:\my db
specs\MYSERVER.pubs, parsing of XML comments is
turned on, timeout is set at 120 sec, and quiet mode is turned on.
Another example:
SqlSpec.exe /c "server=myserver;database=mydb;user id=myuser;pwd=mypass:server=(local);database=pubs;Trusted_Connection=yes;" /n MyDocs
In this example, documentation will be generated from two databases and
placed into a single MyDocs.chm file.
Yet another example:
SqlSpec.exe
/b "c:\my db
files\pubs.xml,c:\my db files\northwind.xml"
/o "c:\my db files\output" /n NorthwindAndPubs
/r 0
In this example, pubs.xml and northwind.xml will
be parsed and a chm named NorthwindAndPubs.chm
will be produced and placed in c:\my db files\output. No pie charts for table sizes will be
drawn. No queries to any SQL servers are
performed.
Starting
with SqlSpec 3.0, you can document any Analysis
Server 2005 or 2008 database using SqlSpec. To do it, use the /C
command line arg. For example:
SqlSpec.exe /C "Data
Source=SUMMER; Initial Catalog=AmoAdventureWorks" /o
c:\analysis_server_specs
You can also combine the spec
for a SQL Server database and a Analysis Server database
into a single chm:
SqlSpec.exe /C "Data
Source=SUMMER; Initial Catalog=AmoAdventureWorks" /c
server=SUMMER;database=AdventureWorks;trusted_connection=yes; /o
c:\analysis_server_specs
Starting
with SqlSpec 5.0, you can document any Report Server
2005/2008 instance using SqlSpec. To do it, use the /rs command line arg.
For example:
To specify the user/pass SqlSpec will use to connect to SSRS, use the /rsu and /rsp switches:
SqlSpec.exe /rs http://SPRING/reportserver /color 1 /o c:\output /rsu MYDOMAIN\Jesse /rsp
Password01!
You can also combine the spec
for a SQL Server database and a Report Server database into a single chm. If you additionally specify that you want
cross-server dependencies documented, SqlSpec will
create dependencies between all the tables, views, procs,
etc in the SQL Server database that are referenced by reports on the Report
Server. This is very useful for
dependency hunting!
SqlSpec.exe /rs http://SPRING/reportserver /c server=SPRING;database=AdventureWorks;trusted_connection=yes;
/color 1 /o c:\output /crossserver 1
To generate documentation for
MS Access databases from the command line, specify an OLEDB connection string
to each mdb file.
If documenting more than one mdb,
separate the connection strings by commas:
SqlSpec.exe /o c:\access_specs /A
"Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=C:\msaccess\AccWebFAQ.mdb,Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=C:\msaccess\Sample.mdb"
SqlSpec will first attempt to use Data
Access Objects (DAO) for documenting Access databases. This will fail if you don’t have DAO360.dll
installed and registered on the client computer where SqlSpec
runs. If it does fail, SqlSpec will let you know and then proceed to document your
Access database using an OLEDB API called GetOleDbSchemaTable.
The reason we use two methods is
because DAO is superior to GetOleDbSchemaTable
for Access because GetOleDbSchemaTable
fails in some circumstances (for example when you have linked tables to remote
data sources in your Access database).
However, DAO is not always installed – GetOleDbSchemaTable
is used in that case as a fallback.
Normally DAO360.dll is installed
here: C:\Program Files\Common Files\Microsoft Shared\DAO\DAO360.dll
If you are up to date with Windows
service packs, you may have it already.
If you do not have DAO installed on your computer, you can get it here: http://support.microsoft.com/kb/q239114. If you have DAO, but it is not registered,
you can register it with regsvr32.exe like this:
Starting
with SqlSpec 3.3, you can document any MySQL 5.0 and above database using SqlSpec. To do it, use the /my
command line arg. SqlSpec uses MySQL Connector/NET
for connecting to MySQL, which you can download here:
http://dev.mysql.com/downloads/connector/net. Note that MySql.Data.dll must reside in the
same directory as SqlSpec.exe.
MySql.Data.dll is the assembly that implements the MySQL
Connector/NET library. Once you install
Connector/NET, just copy MySql.Data.dll to where SqlSpec.exe resides.
For
example:
SqlSpec.exe /my "server=localhost;
database=sakila; uid=someuser; pwd=somepass" /o c:\mysql_specs
Starting
with SqlSpec 3.4, you can document any Oracle 9i and
above schema using SqlSpec. To do it, use the /O
command line arg, passing an OLEDB connection string. All objects in the schema for the user that
you connect as will be documented. For
example:
SqlSpec.exe /O "Provider=OraOLEDB.Oracle;Data
Source=ORCL;User Id=OE;Password=OE" /o c:\oracle_specs
By default when documenting
Oracle databases, only the objects in the default schema of the user specified
in the connection string are documented.
If you want to objects in a schema other than the default schema of a
given user, you can use the /OS flag. In
the following example, all objects in the SYS and SYSTEM schemas will be
documented. In the case of SYS, the
credentials of PowerUser1 are used, and for SYSTEM, the credentials of
PowerUser2 are used.
SqlSpec.exe /O "Provider=OraOLEDB.Oracle;Data
Source=ORCL;User Id=PowerUser1;Password=asdf1234:Provider=OraOLEDB.Oracle;Data
Source=ORCL;User Id=PowerUser2;Password=qwerty7890" /OS SYS:SYSTEM /o
c:\oracle_specs /n sys_and_system
Starting with SqlSpec 3.5, SqlSpec will
document any DB2 database. It has been
thoroughly tested on DB2 8.2, but may work on earlier versions as well. If you try it on an earlier version and it doesn’t
work, let us know and we’ll help you to get it working.
To document a DB2 database, use
the /DB2, /DB2U, and /DB2P command line arguments, described above in the command line arguments section. SqlSpec uses OLEDB
to connect to DB2, so you must specify an OLEDB connection string in the /DB2
command line argument. Here is a sample:
SqlSpec.exe /DB2 "Provider=IBMDADB2;Database=SAMPLE;Hostname=SUMMER;Protocol=TCPIP;Port=50000;Uid=username;Pwd=password"
/DB2U username /DB2P password
Of course you can generate docs
for databases in other DBMS in the same chm by using
other switches. This command line would
generate a DB2 and SQL Server spec in the same chm,
with 20 sample rows from each table and view included in the docs:
SqlSpec.exe /c "server=SUMMER;database=AdventureWorks;trusted_connection=yes;"
/DB2
"Provider=IBMDADB2;Database=SAMPLE;Hostname=SUMMER;Protocol=TCPIP;Port=50000;Uid=username;Pwd=password"
/DB2U username /DB2P password /R 20
Starting
with SqlSpec 3.5, you can document any PostgreSQL 8.0 and above database using SqlSpec. To do it, use the /PG
command line arg. SqlSpec uses ODBC for connecting to PostgreSQL,
so you need to install an ODBC driver for PostgreSQL
on the client machine where SqlSpec runs. You should already have one installed if you
have the PostgreSQL client tools installed.
Below is
a sample of how to document a PostgreSQL
database. The /PG flag contains a
colon-separated list of connection strings, and the /PGDUMP flag specifies the
path to pg_dump.exe, which is used to extract DDL for the objects in your
database. If you don’t
pass /PGDUMP, no DDL will appear in your docs.
SqlSpec.exe /PG "Driver={PostgreSQL
UNICODE};Server=localhost;Port=5432;Database=dellstore;Uid=postgres;Pwd=password;"
/PGDUMP "C:\Program Files\PostgreSQL\8.2\bin\pg_dump.exe" /o c:\postgresql_specs
Starting
with SqlSpec 3.6, you can document a Sybase ASE
12.0.0.4 and above database using SqlSpec. To do it, use the /SY
command line argument. SqlSpec uses Sybase.Data.AseClient.dll for connecting to
Sybase ASE, so you need to install it on the client machine where SqlSpec runs. You
should already have it installed if you have the Sybase client tools
installed. If you don’t have it
installed, you may need to run the Sybase ASE installer again.
Below is
a sample of how to document a Sybase ASE database. The /SY flag contains a colon-separated list
of connection strings, and the /DGPATH flag specifies the path to ddlgen.bat, which is used to extract DDL for the objects in
your database. If you
don’t pass /DGPATH, no DDL for tables will appear in your docs. If you pass /DGPATH, you must also pass
/DGUSER, /DGPASS, and /DGSERVER. These
are the other arguments that are passed to ddlgen.bat
which specify a user, password, and server to connect to.
SqlSpec.exe /SY "Data Source=SUMMER;Port=5000;Database=pubs3;Uid=sa;Pwd="
/DGPATH "C:\sybase\ASEP\bin\ddlgen.bat" /DGUSER sa
/DGPASS abcd?1234! /DGSERVER summer:5000 /o c:\sybase_specs
You may initially have some
trouble getting SqlSpec to connect to your Sybase ASE
server using AseClient. If so, try this:
After following these steps,
SqlSpec should be able to document Sybase ASE databases.
Starting with SqlSpec 4.0, you can document a Sybase SQL Anywhere 10
database. Older versions of SQL Anywhere
may work as well, but only version 10 has been tested. If you have an older version of a SQL
Anywhere database, please try it out and let us know your results. If it doesn’t work, we’ll work with you to
get it working if possible!
To do it, use the /SA command
line argument, or enter an ODBC connection string to a SQL Anywhere database in
the GUI. If using the
command line, you must pass an ODBC connection string in the /SA argument. Example:
SqlSpec.exe /SA "Uid=dba;Pwd=MyPassword;Dsn=SQL
Anywhere 10 Demo;" /o
c:\sql_anywhere_specs
Starting
with SqlSpec 3.7, you can document any Informix 9.4
and above database using SqlSpec. To do it, use the /IFX
command line argument.
Below is
a sample of how to document an Informix database. The /IFX flag contains a colon-separated list
of connection strings. The /IFXDBS flag
specifies the path to dbschema.exe on your system – it is used to generate DDL
for the tables in your database. The
/IFXCMD flag specifies the path to a batch file that defines various
environment variables such as %INFORMIXSERVER%, etc. Without these environment variables being
defined, dbschema.exe will not work properly.
If you don’t pass /IFXDBS or /IFXCMD, DDL for tables will not appear in
your docs.
SqlSpec.exe /IFX
"Provider=Ifxoledbc.2;Password=Zebra!01;User ID=informix; Data
Source=stores_demo@summer; Persist Security Info=true;" /IFXCMD "C:\Program
Files\IBM\Informix\summer.cmd"
/IFXDBS "C:\Program Files\IBM\Informix\bin\dbschema.exe" /o c:\informix_specs
Starting
with SqlSpec 3.7, you can document VistaDB 3.0 databases using SqlSpec. To do it, use the /VDB
command line argument. VistaDB is an in-process database engine implemented in
managed code, available here: www.vistadb.net.
Below is
a sample of how to document a VistaDB database. The /VDB flag contains a list of VistaDBConnection connection strings, separated by the
chart you specify in the /delim argument. If /delim is not
passed, a comma is assumed.
Finally,
SqlSpec requires that a copy of VistaDB.NET20.dll is
located in the same directory as SqlSpec.exe.
This DLL implements the VistaDB engine and is
located here in a default install of VistaDB:
C:\Program Files\VistaDB 3.0\Frameworks\NET
2.0\Runtime.
SqlSpec.exe /VDB "Data Source=C:\Program Files\VistaDB
3.0\Data\DBDemos.vdb3; Open Mode=SharedReadOnly" /o c:\vistadb_specs
Starting
with SqlSpec 3.7, you can document Polyhedra databases using SqlSpec. To do it, use the /POLY command
line argument. Polyhedra is
an in-memory database for high-availability systems made by Enea Software.
Below is
a sample of how to document a Polyhedra
database. The /POLY flag contains a list
of OLEDB connection strings. The /CLC
flag specifies the path to clc.exe on your system – it is used to generate DDL
for the tables in your database. If you don’t pass /CLC, DDL for tables will not appear in your
docs.
SqlSpec.exe /POLY "Provider=PatOleDB.PatOleDB.1;Data
Source=localhost:8001" /CLC
"C:\Polyhedra\poly7.0\win32\i386\bin\clc.exe" /o c:\poly_specs
Starting
with SqlSpec 4.1, you can document Raima RDM Server databases using SqlSpec. To do it, use the /RDM command
line argument. RDM Server is a
RDBMS made by Birdstep
Technology.
Below is
a sample of how to document a RDM Server database. The /RDM flag contains a list of ODBC connection
strings, separated by colons if you want to document more than one
database.
If you
want to include DDL for tables, indexes, and joins, you must also pass the path
to ddlgen.exe in the /RDMDDL flag, as well as the username and password to use
in the call to ddlgen.exe. These are
passed in the /RDMDDLU and /RDMDDLP flags, as in the example below. If you do not pass these three flags, DDL for
tables, indexes, and joins will not appear in your docs.
SqlSpec.exe /RDM "DSN=RDMServer;UID=admin;PWD=adminpass;DATABASE=sales;" /RDMDDL
"C:\Program Files\RDMs8.1\bin\ddlgen.exe"
/RDMDDLU admin /RDMDDLP secret
This section applies to SQL
Server only.
If you want descriptive comments
for each database object to appear in your documentation, then these comments
must first exist in the database as “extended properties”. By default, if there is no extended property
for a particular database object, then the default description that appears in
the documentation is something like “none”.
To improve on this, you need to add an extended property to your
object. To do so, use the MS provided
stored procedure sp_addextendedproperty. You must name the extended property “MS_Description” for SqlSpec to
pick it up. Here’s an example:
-- add an extended
property to the authors table
USE pubs;
EXEC sp_addextendedproperty
'MS_Description',
'here is a nice comment about the authors table',
'user', dbo,
'table', authors
-- add an extended property
to the address column in the authors table
EXEC sp_addextendedproperty
'MS_Description',
'here is a nice comment about the address column in the
authors table',
'user', dbo,
'table', authors,
'column', address
-- add an extended property
to the @percentage parameter
-- of the byroyalty
stored proecedure
EXEC sp_addextendedproperty
'MS_Description',
'here is a nice comment about the @percentage param',
'user', dbo,
'procedure', byroyalty,
'parameter', '@percentage'
--
-- add some xml
comments for a table. using this method
you can add
-- xml comments to
objects that don't store their DDL on the server
-- in sys.comments,
such as tables.
--
EXEC sp_addextendedproperty
'xml_comment','
<summary>This is just
a comment to give a summary of what the jobs table is for.</summary>
<historylog>
<log revision="1.0" date="08/18/2007"
bug="none" email="jesse">Created</log>
<log revision="1.1" date="09/19/2007"
bug="1234" email="jesse">fixed bug 420247</log>
</historylog>
<scope>internal</scope>
<logic>step
1</logic>
<logic>step
2</logic>
<logic>step
3</logic>
<samples>
<sample>
<description>here is some sample
code</description>
<code>select * from jobs</code>
</sample>
<sample>
<description>some more
samples...</description>
<code>select top 10 * from jobs</code>
</sample>
</samples>',
'schema', dbo,
'table', jobs
You can add comments to table
and view columns, stored procedures, user defined functions, etc in this
way. See SQL Books Online http://www.microsoft.com/sql/techinfo/books.mspx
for more info on sp_addextendedproperty, or try google: http://www.google.com/search?q=sp%5faddextendedproperty.
In the case of stored procedures, user defined functions, and views, you also have the option of using XML comments in the SQL code that will be picked up and parsed by SqlSpec.
In the last example, an extended
property is used to add XML comments to a table.
This
section applies to SQL Server only.
It is also
possible to edit extended properties on your SQL Server database objects
directly from the documents generated by SqlSpec. To do this, use the /E ad
/EP command line arguments. If
you pass /E 1, an “edit” link will appear next to each editable comment field
for each object in the documentation. If
you click on this link, you will see a text box where you can type your comment
for the object, together with a cancel and submit button. If you hit submit, the following variables
will be posted to a page (in the query string) that you specify in the /EP
command line argument:
|
Name |
Description |
|
server |
The SQL Server instance that the database resides on |
|
database |
The name of the database. You can use the server and database params to construct a connection string to the database
that contains the extended property in question. |
|
text |
The value of the extended property |
|
propName |
The name of the extended property (usually “MS_Description”) |
|
level0type |
Value to be passed to sp_addextendedproperty
in @level0type param |
|
level0name |
Value to be passed to sp_addextendedproperty
in @level0name param |
|
level1type |
Value to be passed to sp_addextendedproperty
in @level1type param |
|
level1name |
Value to be passed to sp_addextendedproperty
in @level1name param |
|
level2type |
Value to be passed to sp_addextendedproperty
in @level2type param |
|
level2name |
Value to be passed to sp_addextendedproperty
in @level2name param |
Using these
parameters, you can construct a connection string in an asp or aspx page (or similar technology, such as php) and execute a call to sp_addextendedproperty
or sp_updateextendedproperty to update the
appropriate property on your live server.
In this way, your generated documentation becomes a powerful extended
properties editor. Then, the next time
you run SqlSpec against your database,
all your comments will appear in the generated docs.
You can
find an example asp page implemented in VBScript here: http://www.elsasoft.org/samples/save_description.asp.txt
You can
look at that file as an example of how you might want to implement your own
handler page for editing extended properties from the SqlSpec
generated docs. This sample asp page
uses Windows authentication (trusted_connection=yes)
to build a connection string to the database, so if you use it you’ll have to
use “Integrated Windows Authentication” on your IIS server. Either that, or
you’ll have to grant access on your SQL Server to the account that IIS is
running under (which is not recommended!).
Also, it
should be noted that this sample asp does not save the property to the html
files generated by SqlSpec, or to the chm if you are editing properties from a compiled help file
(this is not possible without recompiling the chm). It only saves them to the database. So if you want to get the extended properties
permanently in your documentation, you need to run SqlSpec
again, after you have made all your edits.
The only
requirement of the handler page is that it returns the string “OK” to the
caller when it successfully creates or updates an extended property. This is how the calling HTML page determines
if the property was created/updated successfully.
Normally,
in the /EP flag you would specify a page hosted by a web server on your
company’s intranet, such as: http://MYSERVER/sqlspec/prop_handler.asp,
where MYSERVER is the name of the web server hosting the page.
It would
be unusual, but possible, to have the page that handles the edits to be exposed
to the internet at large, eg: http://www.mycompany.com/sqlspec/prop_handler.asp. If you did this, it means anyone in the world
could potentially edit extended properties in your database. Needless to say this is not recommended!
If you use this feature, SqlSpec turns your database spec into a powerful extended properties editor.
By default, SqlSpec
will generate documentation for all objects in your database that were not
shipped by Microsoft – system stored procedures and the like are automatically
excluded. To exclude other objects, you
need to define an exclusion list.
If you are using the GUI, you
can also exclude objects by clicking the “Objects…” button and choosing the
objects that you want to document.
If you are working from the
command line, or don’t want to have to click the “Objects…” button, then read on. The
exclusion list is composed in XML and is part of the optional config file that SqlSpec uses. Here’s
an example:
<exclude>
<server name="MYSERVER">
<database name="MyDatabase">
<!--
For MYSERVER.MyDatabase, we will exclude
spGetData,
tableEmployee, viewSomeStuff,
and fnDoWork.
For spGetData, we will exclude from the docs the code only, and
not
other details about the sproc
(such as its params, etc).
-->
<object xtype="P" name="[dbo].[spGetData]" excludeCodeOnly="true" />
<object xtype="U" name="[dbo].[tableEmployee]"/>
<object xtype="V" name="[dbo].[viewSomeStuff]"/>
<object xtype="TF" name="[dbo].[fnDoWork]"/>
</database>
<database name="SomeOtherDatabase">
<!--
For MYSERVER.SomeOtherDatabase, we will exclude
spAddRowToTable and spCountRowsInTable.
-->
<object xtype="P" name="[dbo].[spAddRowToTable]"/>
<object xtype="P" name="[dbo].[spCountRowsInTable]"/>
</database>
</server>
<server name="OTHERSERVER">
<database name="OtherDatabase">
<!--
For OTHERSERVER.OtherDatabase, we will exclude spDoStuff.
-->
<object xtype="P" name="[dbo].[spDoStuff]"/>
</database>
</server>
</exclude>
As shown in the example, you can
exclude stored procedures, tables, views, and user defined functions from being
documented. Some things to note:
Because the values in the name
attributes are case sensitive, if you were trying to exclude “sp_getdata” from MYSERVER.MyDatabase
and you used the sample above, it would not be excluded. You would have to change spelling in the
sample above from “sp_GetData” to “sp_getdata”.
You can also exclude the code
for entire classes of objects, or for all objects at once using a global value
for the excludeCodeOnly attribute on the root
<exclude> element. You do it like
this: if you want to exclude all the DDL for all objects from your docs, your
<exclude> element would look like this:
<exclude excludeCodeOnly="true">
To selectively exclude the DDL
for certain types of objects and not others (without having to specify the name
of each as you would above), just specify the xtypes
of the objects you want to exclude code for.
For instance, this would exclude the DDL for all procs
and functions:
<exclude excludeCodeOnly="P,PC,X,FN,IF,TF,FS,AF,FT,XMLA,MDX">
This would exclude the DDL for
all tables and views:
<exclude excludeCodeOnly="U,V">
This would exclude the MDX and
XMLA code in an Analysis Server database:
<exclude excludeCodeOnly="XMLA,MDX">
Each xtype
in the attribute value should be separated
by a comma, with no spaces. See the xtypes section below for a list
of all the values you can put in the excludeCodeOnly
attribute.
Here’s a table of object types
and their xtypes that may be excluded from the
docs. Many of the objects only exist on
SQL 2005 servers (such as all the CLR objects).
|
Object |
Xtype |
|
Table |
U |
|
View |
V |
|
Sproc |
P |
|
CLR sproc |
PC |
|
Extended sproc |
X |
|
Scalar udf |
FN |
|
Tabled valued udf |
TF |
|
Inline udf |
IF |
|
CLR Aggregate udf |
AF |
|
CLR scalar udf |
FS |
|
CLR table valued udf |
FT |
|
Rule |
R |
|
Synonym |
SN |
|
CLR trigger |
TA |
|
Trigger |
TR |
|
Schema |
SCHEMA |
|
User defined type |
UDT |
|
Certificate |
CERTIFICATE |
|
Xml schema collection |
XML_SCHEMA_COLLECTION |
|
Symmetric key |
SYMMETRIC_KEY |
|
Asymmetric key |
ASYMMETRIC_KEY |
|
Assembly |
ASSEMBLY |
|
Package |
PACKAGE |
|
Domain |
DOMAIN |
|
Materialized view |
MATERIALIZED_VIEW |
|
XMLA |
XMLA code (Analysis Server) |
|
MDX |
MDX code (Analysis Server) |
The last few don’t have xtypes defined in SQL Server, so I made up my own – these
are all the xtypes longer than two characters. Also, many of these only make sense for
certain platforms. For instance,
packages only exist in Oracle and DB2, and not any of the other platforms
supported by SqlSpec.
Data model diagrams are
graphical representations of data that involve more than one database or
external object. They are linked
together via dependencies and primary/foreign key relationships. If you want to have such diagrams generated
and placed in your documentation, you need to define a <models> section
in your optional config
file. Data model diagrams can
include tables, views, stored procedures, user defined
functions, as well as any external objects
that you include in the documentation.
The idea is similar to the
diagrams that you can make with Enterprise Manager in SQL Server 2000, except
that you can include more than just tables in the diagrams, and you can include
objects from different databases on different servers in the same diagram. Also, in Enterprise Manager, the links are
made via primary/foreign key relationships only, whereas in SqlSpec,
dependencies are used (in addition to primary/foreign key relationships) to
link objects together in the diagram.
SqlSpec uses a
statistical algorithm (simulated annealing) to try and find the optimal
placement of the object icons such that the number of link crossings, the
length of all the links, and (optionally) the number of overlapping links, is
minimized. This makes the diagram more pleasing
to the eye than just randomly placing the icons on the screen.
In the generated chm file, you will find all your data models in the left
hand navigation tree (contents tab) collected under a node entitled “Data
models”.
When the htm
files for your data model diagrams are generated, they are placed in a
directory named “models”. This directory
is created in the output folder that you specify with the /o command line flag.
Here’s an example of a
<models> section for a set of documentation including both the pubs and northwind databases:
<models>
<model name="Pubs stuff"
description="This data model shows objects in the pubs database"
iconsPerRow="6"
seed="1"
allowOverlap="0"
horizontalSpace="75"
verticalSpace="75"
maxLabelLength="12">
<server name="MYSERVER" type="SqlServer">
<database name="pubs">
<object xtype="U" name="[dbo].[authors]"/>
<object xtype="U" name="[dbo].[titleauthor]"/>
<object xtype="U" name="[dbo].[publishers]"/>
<object xtype="U" name="[dbo].[employee]"/>
<object xtype="ComObject" name="Com object 1"/>
<object xtype="WebPage" name="Web Page 1"/>
</database>
</server>
</model>
<model name="Northwind and pubs" iconsPerRow="8" seed="2" allowOverlap="1" horizontalSpace="100" verticalSpace="80" maxLabelLength="16">
<server name="MYSERVER" type="SqlServer">
<database name="Northwind">
<object xtype="U" name="[dbo].[Orders]"/>
<object xtype="U" name="[dbo].[OrderDetails]"/>
<object xtype="ComObject" name="Com object 1"/>
<object xtype="WebPage" name="Web Page 1"/>
</database>
<database name="pubs">
<object xtype="U" name="[dbo].[authors]"/>
<object xtype="U" name="[dbo].[titleauthor]"/>
<object xtype="U" name="[dbo].[publishers]"/>
<object xtype="U" name="[dbo].[employee]"/>
</database>
</server>
</model>
</models>
The structure of this XML is very
similar to the exclusion list. Some
things to note:
o
The “name” attribute is required and specifies the
name of the data model.
o
The “description” attribute is optional. It should contain some longer text to
describe the model.
o
The “iconsPerRow”
attribute is optional and specifies the maximum number of icons (there is one
icon for each object) will fit on a single row.
In the diagram, icons are placed on a grid, so this value says how many
icons could appear in a single row if all the positions in the row were
occupied. If you don’t specify it, the default value is 8.
o
The “seed” attribute is an integer that is used to
seed a random number generator that is used in the icon placement algorithm
that SqlSpec uses.
The default value is 1. If you
pass a different value, the positions of the icons will change. If a particular placement doesn’t appeal to
you, try changing the seed and see how things change.
o
The “allowOverlap”
attribute specifies if you want to allow the lines in the diagram that
represent links between objects to overlap or not. A value of 1 means allow, 0 means don’t allow
overlap. The default is 0.
o
The “horizontalSpace”
attribute specifies how much space you want to leave between icons in the
horizontal direction. Default is 80.
o
The “verticalSpace”
attribute specifies how much space you want to leave between icons in the
vertical direction. Default is 80.
o
The “maxLabelLength”
attribute specifies how many characters of an icon’s name you want to show as
its label. The default value is 8. That means that if your objects name is “MyLongNamedObject” it would appear as “MyLongNa…”
if you leave the default. However, when
you hover over the icon with the mouse, you will see the fill name of the
object.
o
SqlServer
o
AnalysisServer
o
Oracle
o
MySQL
o
PostgreSQL
o
DB2
o
Sybase
o
Access
o
Informix
o
VistaDB
o
Polyhedra
Since you probably don’t want to
type in all this xml by hand, SqlSpec will create an
xml file for you in the output directory called allobjects_datamodel.xml
each time it runs. This is a sample config file with a single datamodel
defined in it, with all objects in the database. You can just copy/paste from this file to
create your own models, so you don’t have to type. You can even pick and choose objects from
different databases and different servers and combine them into a single
model.
One final note: the time needed to place the icons increases rapidly (like n2) with the number of links connecting the icons. So be prepared to wait if you define a data model with 100 icons that are all linked together in a complicated way. In my experience, it takes a few seconds for ~10 icons, about a minute for ~20, and several minutes for 50. In any case, if you data model has more than 50 objects in it, you probably need to rethink your data model anyway. J
Databases do not exist in a
vacuum. Every database, if it is to be
of any use to anyone, has a number of clients that depend on it. These clients take the form of external
objects, such as COM objects, .NET assemblies, Java classes, Web Services, SQL
scripts, ASP/ASP.NET/PHP/JSP pages, an executable file, or whatever you
like. A web application utilizing .NET,
for example, may consist of a SQL database, a couple web services, and several
ASP.NET pages. The web services likely
will make calls to the SQL database to get their work done. This means that the web services are
dependent on various database objects.
You may even have stored procedures in your database that make calls to
external objects, making your stored procedures dependent on these external
objects.
If you define your external
objects in XML, then SqlSpec will parse that XML and
include your external objects in the documentation that it generates. In this way, you can generate a single chm that incorporates every object and dependency in your
solution.
When the htm
files for your external objects are generated, they are placed in a directory
named “ExternalObjects”. This directory is created in the output
folder that you specify with the /o command line flag.
There are two parts to
incorporating external objects into the documentation:
Let’s look at the
<definitions> part first: