SqlSpec

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!

What it does

SqlSpec is a tool for generating documentation for any database on the following platforms:

Microsoft SQL Server 2000/2005/2008
Microsoft Analysis Server 2005
Microsoft Access 97 and above
Oracle 9i and above
IBM DB2 8.2 and above*
IBM Informix IDS 10 and above*
Sybase 12.0.0.4 and above*
Sybase SQL Anywhere 10.0 and above*
PostgreSQL 8.0 and above
MySQL 5.0 and above
VistaDB 3.0 and above
ENEA Polyhedra 7.0 and above
Birdstep’s Raima RDM Server 8.0 and above

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

Extending the documentation to cover external objects, such at Java packages, COM, .NET assemblies, Web Services, scripts, etc. You must specify your external objects and how they depend on the internal database objects in separate XML files.
Specify an exclusion list to exclude certain objects you specify from appearing in the documentation. This is useful if there are certain stored procedures that you don’t want to expose to the consumers of the documentation. You might not want everyone reading the code for sp_GiveEmployeeBigFatRaise, for example.
Custom navigation hierarchies in the chm navigation tree.
Collecting various properties of the server the database resides on.
Documents all SQL jobs on the server, if the user has permissions to execute msdb..sp_help_job and its relatives.
Shows a call tree for sprocs and udfs that call other sprocs and udfs.
Documents SQL Server 2005 objects such as Assemblies, Symmetric and Asymmetric Keys, Certificates, XML Schema Collections, and the like.

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.

Requirements

The .NET framework (version 2.0) must be installed on the machine where you run the SqlSpec.exe. Get it here if you don’t have it: http://msdn2.microsoft.com/en-us/netframework/aa731542.aspx.
To generate specs for Analysis Services specs, you must install Analysis Management Objects (AMO). AMO is the managed code library that wraps all of AS 2005 (it’s used heavily by SSMS, so if you have the SQL Server 2005 client tools installed, you already have it). You can get AMO here if you don’t have it: http://www.microsoft.com/downloads/details.aspx?familyid=B33D2C78-1059-4CE2-B80D-2343C099BCB4&displaylang=en (search for “AMO” on that page).
It is not necessary to have the .NET framework installed on the server where your database resides. In fact, your server may not be running Windows at all so this would be impossible anyway in that case.
If you want to compile the output into CHM files, you need the Microsoft HTML Help Workshop installed on the client. You may already have it installed. SqlSpec will detect if you have it installed or not. If you don’t have it, it will ask you if you want to install it. If you say yes, it will download and install it. Alternatively you can get it yourself from here: http://go.microsoft.com/fwlink/?LinkId=14188.
To generate documentation for databases on MySQL 5.0 and above, you must have MySQL Connector/.NET installed on the computer where SqlSpec runs. You can get it here: http://dev.mysql.com/downloads/connector/net. After you install it, you need to copy MySql.Data.dll to the directory where SqlSpec.exe is located.
To generate documentation for Oracle schemas, you need to have an Oracle OLE DB driver installed on the computer where SqlSpec runs. You can download a driver from Oracle here: http://www.oracle.com/technology/software/tech/windows/ole_db/index.html.
To generate documentation for Sybase ASE databases, you need to install AseClient, a .NET data provider for Sybase ASE. This component is available in the Sybase ASE SDK, and also ships with ASE itself: https://sybase.subscribenet.com.

Quickstart

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.

Click on SqlSpec.exe to launch the GUI. You can also run it from the command line passing command line arguments, see the command line arguments section below.
Enter the connection string(s) to the database(s) you want to document. For sample connection strings, see: www.connectionstrings.com.
Hit the Go! Button. As it does its work, it will print out progress. You can cancel while it’s running by hitting the Cancel button.
When it’s finished, click on the “View .chm” button to see your documentation. By default the output is placed in a folder called “output” in the same directory as SqlSpec.exe.

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.

Command line arguments

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 xml files 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. 0 is 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.

Supported Platforms

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.

Microsoft SQL Server 2000/2005/2008

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.

Microsoft Analysis Server 2005/2008

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

Microsoft Report Server 2005/2008

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:

SqlSpec.exe /rs http://SPRING/reportserver /color 1 /o c:\output

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

Microsoft Access

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:

open a console window
cd to the directory where DAO360.dll is located.
type “regsvr32 DAO360.dll” and press enter
you should see a confirmation that the DLL was registered successfully.

MySQL

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

Oracle

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

DB2

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

PostgreSQL

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

Sybase ASE

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:

First, make sure you have a copy of Sybase.Data.AseClient.dll on your computer. Often it will be located here: %SYBASE%\DataAccess\ADONET\dll. You should also find sybdrvado115.dll, msvcp71.dll and msvcr71.dll in that same directory.
Copy sybdrvado115.dll, msvcp71.dll and msvcr71.dll to the C:\WINDOWS\system32 directory. You can find them in the same directory as Sybase.Data.AseClient.dll.
Copy Sybase.Data.AseClient.dll into the directory where SqlSpec.exe is located.

After following these steps, SqlSpec should be able to document Sybase ASE databases.

Sybase SQL Anywhere

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

Informix

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

VistaDB

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

Polyhedra

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

Raima RDM Server

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

Descriptive comments

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>

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

<description>here is some sample code</description>

<code>select * from jobs</code>

</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.

Editing Extended Properties

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.

Exclusion lists

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:

<!--

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).

-->

</database>

<!--

For MYSERVER.SomeOtherDatabase, we will exclude

spAddRowToTable and spCountRowsInTable.

-->

</database>

</server>

<!--

For OTHERSERVER.OtherDatabase, we will exclude 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:

Under the <exclude> element, you can have any number of <server> elements.
Under the <server> elements, you can have any number of <database> elements.
Under the <database> elements, you can have any number of <object> elements each needs an xtype and name attribute to say what the type of object is, and what its name is.
For objects that have related code, such as views, sprocs, and udfs, you can use the excludeCodeOnly=”true” attribute to say you want to keep the object in the documentation, but don’t include the code for it.
On all elements, the “name” attribute is required.
The values of the “name” attributes on all elements are case sensitive.
The <exclude> element must appear as a child of the root <SqlSpec> element in the config file.
If you want to exclude the code for ALL objects in the spec, put excludeCodeOnly=”true” as an attribute on the top level <exclude> element.

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”.

Global excludes

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:

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:

This would exclude the DDL for all tables and views:

This would exclude the MDX and XMLA code in an Analysis Server database:

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.

xtypes

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 models

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:

<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">

</database>

</server>

</model>

</database>

</database>

</server>

</model>

</models>

The structure of this XML is very similar to the exclusion list. Some things to note:

Under the <models> element, you can have any number of <model> elements.
Each <model> element describes a particular data model diagram:

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.

Under each <model> element, you may have any number of <server> elements. This means you can have data model diagrams that incorporate database objects from different databases on different servers.
Under each <server> element, you may have any number of <database> elements.
Under each <database> element, you can have any number of <object> elements. The <object> element is used to specify any database object together with the xtype attribute, and also any external objects.
The “name” attribute is required on all elements except for <models>
The “xtype” attribute is required on <object> elements.
The value of the “name” attribute is case sensitive in all cases.
The <models> element must appear as a child of the root <SqlSpec> element in the config file.
The “type” attribute is required on the server element. Allowed values are:

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 n²) 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

External Objects

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:

First, you must declare each type of external object that you want to represent in the <definitions> element of the config file.
Second, for each external object that you want to include, you must compose some html that you want to be displayed for that object, as well as define the dependencies of that object on all other objects.

Let’s look at the <definitions> part first: