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