Spotlight on ApexSQL Doc : SQL Server Documentation Tool

Creation Options
You select this option, when you want to have documented options like “QUOTED_IDENTIFIER” or “ANSI_NULLS”.

Statistical Data Analysis
This option documents the statistical information of the tables. Documentation includes Page Statistics, Extent Statistics and Row Statistics. It is not too hard to guess, that including this option, will increase the time the documentation process takes. So, you might want to carefully evaluate if you need this option at all (it is the only option disabled by default of this settings page of ApexSQL Doc) or consider including this option when you decide the run by documentation task via the Command Line in a job scheduled for times of less or none database activity.

Suppose you initially use ApexSQL Doc to document all of your existing databases. In that case you certainly appreciate the “Import” and “Export” buttons. They allow you to export the current selection to an XML file, which in turn you can load into any other project or save for such documentation purposes as to illustrate the evolution of a database over time. Using this feature is not only less time-consuming, but also less prone to errors as you are less in danger to forget to select some settings. Note, that “current selection” not only includes the list of object types chosen at this step, but also includes the filters on these objects types, which I am about to define now.

For all database object types chosen in the previous step you can use filtering to narrow down by name or pattern the objects you want to document. Just check the appropriate box to make the corresponding item in the Treeview visible or simply use the “Check All” or “Uncheck-All” checkboxes. After you’ve made your selection of objects types you want to apply filtering on, you select the corresponding item in the Treeview. This brings you to the actual filter where you can enter your patterns which a database object name of that given type must match to be included in the documentation. This filtering mechanism is the same as in all the other ApexSQL tools I’ve reviewed this far. It works by means of regular expressions. To demonstrate how this filtering process looks like, I’ve chosen to filter on tables names.

Suppose we only want to include certain tables that contain the patterns N*:

After typing in the pattern, ApexSQL Doc is displaying the tables that match these patterns in the right list. From there you can choose which table will be included in the final documentation.

Another notable feature of ApexSQL Doc is that the style in which the final documentation is created, is not static or predefined. You are able to use a custom style sheet that will be used for the documentation. By default, the tool includes a style sheet that mimics the Visual Studio 2005 style. The corresponding css file ships with the software, so that you can have a look at it, when you decide to use your own for your documentation. We will see some examples of this final output a little bit later on.

Optional Items

The “Optional Items” settings page contains some interesting options, such as the “Graphical Dependencies” option or the object grouping by file group.

Depending on your selections the Treeview might get adjusted and show or hide some items. Since I want to include the “Graphical Dependencies” in my final output, I need to check the appropriate checkbox, which in turn will result in that the Treeview item which gives me access to the corresponding settings page is visible.

The “Graphical Dependencies” functionality is powered by the same engine that is implemented in ApexSQL Clean.

I’ve described the engine in more detail in my paper on the ApexSQL Clean tool, so if you want to read more about it, you can find it here.

Tagged Comments

To me one of the most interesting features is the “Tagged Comments”, so I will explain it in more details. “Tagged Comments” are custom tags you can use to store individual and specific information about a given database object. They are stored together with the objects SQL statement.

This works for all supported database objects with the exception of tables and indexes. I’ll return to tagged comments on tables and indexes in just a moment. But let me first explain the usage of tagged comments on the other object types. To use tagged comments later on in the documentation process, you directly include the tags when you create an object. For example like this:

-- ##SUMMARY Sample Description1
-- ##SEEALSO TABLE dbo.authors
-- ##ISNEW  09.09.2006
create procedure [dbo].[sp1] @id int, --##PARAM @p1 The p1
    @fname varchar(50) = 'D%', --##PARAM @fname last name
    @salutation varchar(50) ='D%', --##PARAM @salutation salutation
    @position char(10)
select * from dbo.authors

Since the “source code” for objects other than tables and indexes is stored by SQL Server in one of its system tables, ApexSQL Doc can directly retrieve the information from that system table when creating the documentation. However, these things are a bit different for tables and indexes. The generating source code for that kind of objects is not stored inside SQL Server and therefore cannot be directly retrieved. In order to use tagged comments on tables and indexes now, you need to specify one or more *.sql files containing the source code for that objects at the “Tagged Comments” wizard screen as shown in the screen shot above. In a properly organized environment this shouldn’t represent a problem, since you keep these files in some source code managing app anyway.

To see these “Tagged Comments” in action, I’ve deliberately borrowed the following screen shot from the ApexSQL Doc online manual.

Here’s a list of the currently supported tags:


Leave a comment

Your email address will not be published.