Documenting databases

Open any book or article about database development and administration, and I can all but guarantee that you will find a section for database documentation that describes its importance, best practices, conventions, etc. Why is this? How come so much attention is given to an aspect of databases that, at first sight, has nothing to do with their functionality? Those sections in books and articles, provide dozens of arguments in favor of the importance of database documentation, almost too much to remember. However, most of the reasons boil down to the following:

An environment that is less complex.
Lower likelihood of errors
Databases that are easy to maintain and troubleshoot
Low training cost for new staff
Higher productivity

It also offers a common language between IT and business decision makers, and an easy way to find hot spots or areas that are troublesome and could potentially become bottlenecks for the database.

Even though the high importance of documentation is stated and repeated over and over, and is just blatantly obvious, the process of documenting databases is often postponed or overworked by DBAs or developers (myself included). Why? Well, because it is simply too boring. Most DBAs would prefer to perform virtually any other task instead of typing in the descriptions of every element, one by one, in a Word or HTML document. If only there was some kind of tool that would do this instead of having to spend precious human work hours on it. But wait, there is! So we arrive at the goal of this article, to demonstrate how xSQL Documenter reads databases and generates those databases' documentation in Compiled HTML Help document and HTML files.

For this demo, I'm using the AdventureWorks2012 sample database for the simple reason of it being a very well documented database. The process is fairly straightforward. First, you specify the databases you want to document by supplying the connection strings. xSQL Documenter supports all of the popular database engines such as SQL Server, MySQL, Oracle, DB2 etc. (for a full list, have a look here)

Choose the objects in those databases that you want documented:

And then press 'GO'. xSQL Documenter will read each object's type, descriptions (in the case of SQL Server it will read the MS_Description field), relationships with other database objects, and just about any other information it can find about the object. Then, it will use all the information gathered to build the help files. These help files will be saved by default in an 'output' folder on the same location as xSQLDocumenter.exe and will have the following format:

As you can see, the information is grouped first by database, then by object type, and then by schema. Clicking on any object type will display a list of all the objects of that type along with some other data for each object. These data differ for every object type. For example, for tables, it will display the number of columns, indexes, constraints, number of objects it refers to and objects that refer to it and a comment which is taken from the MS_Description field. For views, it will display the number of columns, code length, number of dependents (like indexes), number of objects the view depends on, the row size and a comment, again taken from MS_Description. If you click on any object a new page will open with detailed information about each column in the table.

Below is the detailed view of the documentation for the Employee table.

First it displays a description and data for all the columns, these data include the column's data type, default value, whether it is nullable or not, a description, etc. Further down, you can also see details about the indexes and a list of all the objects that reference the Employee table and object which this table references.

At the end of the page you can see a chart showing the relations among objects. Keep in mind that all objects above the Employee table in the chart are objects that are referenced by this table and all objects below it are objects that reference this table. Besides this chart you also see detailed information about all the constraints and foreign keys for the Employee table.

As demonstrated, with just a few clicks you can generate a full and comprehensive help material that will display info about every database object you want and the relationships between these objects. And, if one has the good sense to provide a description for objects during creation, xSQL Documenter will display that description, making it that much easier to understand the role and function of the object. If this documentation is not to be uploaded in an intranet to be viewed using a browser you can use the .chm file which is indexed and searchable. This makes finding information about a particular object even easier.

This tutorial shows only the tip of the iceberg regarding the capabilities and the information that xSQL Documenter is able to display. For a full reference to this capabilities you can view xSQL Documenter's online help.

Rate

Share

Share

Rate