SQL SERVER – Documentation Tool

This article explains how to use ApexSQL Doc, a SQL documentation tool. You can download ApexSQL Doc, and follow along with this article.

ApexSQL Doc is a SQL Server database development tool, for generating database and SSIS package documentation. It generates documentation in various output formats such as chm, html, doc, and docx. You can include relationship diagrams in a documenting process and customize the preview settings. ApexSQL Doc shows extended properties for all database objects created at the SQL Server level. Using the Extended property editor feature you can manipulate with previously created description or add a new one.

You can choose database objects to include in the documentation, along with the appropriate attributes. Documentation process can be scheduled and automated using the Command Line Interface (CLI). Available output styles can be applied to the output documentation file, or edited additionally.

A new version of ApexSQL Doc 2014 supports SQL Server 2014 and Integration Services 2014. It has a new UI style. Starting from ApexSQL Doc 2014 R2, Windows Azure will be supported, and PDF will be added as a new output format.

Starting the application will open main window where you can start a new project. Clicking the New buttonallows you to add an SQL Server instance, or Integration Services package. You can add multiple SQL Servers as well. To add a SQL Server instance or SSIS package, click the appropriate option marked in the image below:

SQL database documenting

If you choose to connect to SQL Server, the Add SQL Server dialog opens. You can specify local server, or type the address for a remote one, and choose the authentication method. In the Connection options dialog you can increase the values for the Connection timeout and Execution timeout options if you have a slow connection with a remote server.

When a SQL Server is connected, a list of the databases will be shown in the Databases section, where you can select the databases to include in the processing. Check the box for the database you want to add, or select multiple databases from a multiple SQL Servers, to document them at once:

ApexSQL Doc allows you to document server objects. For each combination of the selected server objects, the bitwise set number is generated, and can be used in the CLI, as this number represents a specific object combination, so you don’t have to include the objects manually. For the highlighted object type, a list of the object instances is shown in the object filter section, where you can select a particular instance, and include it in the documenting process. The selected combination of the server objects can be saved as an external file, clicking the Export button, and used later in another project:

In the Database options section, you can set additional options related to database documentation. ApexSQL Doc allows you to document system objects, DDL scripts, and highlight SQL syntax in the output document. In this section, you can set the object grouping (by schema or filegroup), sort objects by the owner/schema section, or include the extended properties. In the Table of Contents section, you can include a Dictionary section for a parameter/column names. Empty sections, or nodes with no relevant metadata existing can be included/excluded from the documentation. Including system objects, DDL scripts, or T-SQL highlighting can slow down the process, as this is performance intensive options:

The next step is to select the database objects to be documented. Checking the box for the appropriate object will include it in the documentation and at the same time will show the list of the object attributes, which you can choose to document. The same as for server objects, the bitwise set number is generated for each combination of the selected database objects, and can be used in the CLI, or the selection can be saved as an external file, and used later in another project:

Server and database objects can be filtered using the Filter editor feature. To enable filtering for the specific object, select the appropriate Use filter checkbox, and the list of the objects will come up under the Object filter tab. Here you can choose the specific objects to document, selecting the related checkbox.

For advanced filtering, click the “…” button, and the Filter editor dialog will open. Here you can specify the filtering conditions to apply to a selected object. Depending on the selected criteria, the objects that satisfy the logical conditions will be included or excluded from the documenting:

ApexSQL Doc allows you to include graphical dependencies in the documentation, and to customize the dependency type and layout settings. Documenting graphical dependencies requires more hardware resources, and can slow down the documenting process. Changing the value of the Scale option will increase/decrease a scale of the graphical dependencies in the output document. This will allow you to improve a visibility of the graphical dependencies in the output document. Object dependencies can be presented as tables, checking the Include dependency list/tables option:

Under the Styles tab, you can select the style to apply to the output document. Choose one of the predefined styles, or edit any of them manually to suit your needs. For a full preview of the output styles, the JavaScript option needs to be enabled for the current Internet zone. Any changes you made will be shown in the Preview section:

Before you generate the documentation, you can edit database extended properties using the Extended property editor. ApexSQL Doc allows you to review any object description added at the SQL Server level, or to add a new description. To add a description click the “…” button, and the popup window will open. Adding the description in the Extended property editor saves it automatically in the database.

Once you have everything set, you can generate the documentation, clicking the Generate button from the Home tab:

SSIS package documenting

ApexSQL Doc allows you to document online (from SQL Server, or Integration Services) and offline (from the file system) SSIS packages. To add a package, under the Integration services tab, click the Add button. When the dialog opens, choose the appropriate package source. New version of ApexSQL Doc supports SSIS 2014. You can include multiple packages from multiple sources, and document them at once:

When the SSIS packages are added, from the Package details tab choose the objects you want to document. You can include package source (*.dtsx) file as well:

All output options (format, style) apply to SSIS package documenting as for the databases. When the SSIS packages documenting is set, click the Generate documentation button from the Home tab.

ApexSQL Doc allows you to automate the documentation process, using the Command Line Interface, and schedule it using SQL Server Job, or Windows Task Scheduler.

Output files

Using the Save as option in MS Word, you can save the documentation file as a PDF file. Currently available output formats are chm, html, doc, docx (pdf through docx).

Here are the examples of the generated documentation:

Reference: Pinal Dave (https://blog.sqlauthority.com)