SqlSpec, Not Just a Documentation Tool

Let us see what you get for sample AdventureWorks database that comes along with SQL Server 2005.

It took little over two and half minutes to create CHM file of above database. For SQL Server 2005 database, you will see CHM like in following format.

First page of CHM will give you summary of database. Summary of database includes count of objects, data and log file information, user information, and properties of your database and server. Sometimes, you need to keep server properties in order to execute some operation in database. Note that to see all the server properties, you must first make sure that the "show advanced options" server option is true. To set it, you can run this code:

exec sp_configure 'show advanced options', 1
reconfigure

For all the objects, you can view the summary of them. There are valuable graphical representations for Tables, stored procedures, views, triggers and user defined functions. For example, following graph shows largest tables by size on disk. From this, DBAs can identify what are the tables that needed to be in separate filegroups.

In triggers, it will be better if it can show whether the trigger is for replication or not for replication and the order of execution. Even though these parameters are rarely used, but they are very essential when the need arises. The author of SqlSpec says this feature will be coming in the next version.

To view the documentation of a table you can select the table from the tree node. Then you will see a page like below.

All the details for columns will be displayed including the comment. Again, comments are not available with most of the tools around.

This screen also has indexes and defaults. Bu clicking the default you will be taken to the default description page.

Another important feature of this tool is script of objects.

Graphical presentations are very rare in other documentation tools, but not in SqlSpec. If you select a table, you can view the relationship between that table and other tables. This is very important feature as a picture can explain thousand words.

Dependency graph for stored procedure is another important feature available with this fantastic tool. It indicates all the stored procedures, views, and table that is depend from the selected stored procedure. Dependency graphs are created for all objects that have dependencies.

You can also generate data model diagrams for your database. It is like the database diagram that you are getting in SQL Server 2000, except that you can include any type of object, as well as objects from different databases in a single diagram. Finally, you can include your own types of objects (such as COM, Web services, etc) in the data model diagrams. You can add the list of objects specify them in command line parameter /d.

There are few samples available in the SqlSpec Web site. You can view them from following links.

• AdventureWorks.
• msdb.

As you are aware, there are many new features available with SQL Server 2005. These SQL Server 2005 specific objects can be documented with SqlSpec: DDL Triggers for Database and Servers, Authentication objects such as Symmetric and Asymmetric Keys, Certificates, Assemblies, CLR procs and udfs, Schemas and XML Schema Collections.