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:

SQL Server 2000/2005
Oracle 9i and above
DB2 8.2 and above*
Sybase 12.0.0.4 and above*
PostgreSQL 8.0 and above*
MySQL 5.0 and above
Analysis Server 2005
Access 97 and above
Informix IDS 10 and above
VistaDB 3.0 and above
ENEA Polyhedra 7.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=DF0BA5AA-B4BD-4705-AA0A-B477BA72A9CB&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. 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.