SqlSpec, Not Just a Documentation Tool

There are many tools in the market arena to document your databases. I believe this is one of the most common types of database tools available. The high number of tools is an indication that this is a required feature for database developers and DBAs. So an obvious question is, is SqlSpec just another SQL Server documentation tool? Well, I won't comment. Check a few SQL Server database documentation tool reviews and then check this. You will find many features in SqlSpec that are missing in other documentations tools. Did I mention SqlSpec costs less, too?

Just to mention a few features that make SqlSpec stand out: documenting SQL Server jobs, documenting SQL Server 2005 objects (such as Assemblies, Symmetric and Asymmetric Keys, Certificates), xml comments, exclusion lists, dependency graphs, and data model diagrams.

Key Features:

• Document your database objects.
• Specify an exclusion list to exclude certain objects you specify from appearing in the documentation. Can also exclude just the code for an object.
• Extending the documentation to cover external objects, such at COM, .NET assemblies, Web Services, scripts, etc.
• Custom navigation hierarchies in the CHM navigation tree.
• Collecting various properties of the server the database resides on.
• Graphical representation of objects relationship and dependency between existing objects in the database.
• Parsing of xml comments in sprocs, udfs, views, and triggers.
• Document all SQL jobs on the server.
• Shows a call tree for stored procedures and user-defined-functions that call other for stored procedures and user-defined-functions.
• Documents SQL Server 2005 objects such as Assemblies, Symmetric and Asymmetric Keys, Certificates and XML Schema Collections etc.
• Create a batch file that can be used to schedule to execute so that your database will be documented automatically.

Installation and Configuration

It is the simplest installation that you will get for a tool. Any IT personal can install this tool. It is a zero configuration tool. Just drop the exe on your system and run it.

Preparation

SqlSpec requires following software and security requirements.

• The .NET framework (version 2.0).
• The database you want the spec for must be a SQL Server 2000 or 2005 database. It is not necessary to have the .NET framework installed on the SQL Server where your database resides.
• The connection strings you provide must be associated with a login or user that has select permissions on the system tables, as well as permissions to create temp tables. To generate documentation on SQL Agent jobs, you must have permission to execute msdb..sp_help_job and you must enable OPENROWSET on your server in case of SQL Server 2005.
• If you want to compile the output into CHM files, you need the Microsoft HTML Help Workshop installed. The first time you run SqlSpec, it will ask you to download HTML Help workshop if you haven't installed it earlier.
• If you want to see the DDL for tables in the generated docs, you must have SMO installed where SqlSpec is running. SMO is available here: Feature Pack for Microsoft SQL Server 2005 — November 2005.

Features

First, there is detailed documentation about all the features in SqlSpec here: www.elsasoft.org/sqlspec.htm.

No fancy, flashy screens are available and you will not find many screens in SqlSpec. Instead, two simple screens are there for you to document your databases.

You need to enter connection string. It will be better if the tool provide an option to generate a connection string. For example, it can provide a simple user-interface like in creating DSN.

If you need to document a database of SQL Server in windows authentication, you need to provide a connection string as

server=(local); database=Northwind; Trusted_Connection=yes

In case of SQL Server authentication, you need to provide connection string like,

server=(local); database=Northwind; uid=sa; pwd=sa

Always you do not want to document your entire database. Most of the time, you may need selected objects to be documented. To facilitate that, there is an option of selecting objects that user wants. By clicking the "Objects…" button, you will get the following screen to select objects you want. This is useful if there are certain stored procedures that you don't want to expose to the consumers of the documentation.

After this, it is just a matter of creating CHM file.

Then just click GO button, CHM file will be created in the folder where SqlSpec available. Here also, it would be better if the GUI provided an option to users to select a folder to create a CHM file. However, if you run it from the command line, the "/o" switch allows you to specify this.