Creating a self documenting Database (MS SQL)
Posted August 2, 2007on:
With SQL Server, it is strange how some of the most radical improvements that have been introduced over the years have been slipped in almost unnoticed. Conversely, the features that were plastered all over the marketing brochures are sometimes the ones that turn out to be dead ends.
A feature that fits into the former category is the extended properties of databases. Introduced quietly with SQL Server 2000, one could have easily missed them but they have proved, after all, to be of great value to the developer. Extended properties are the route to creating self-documenting databases. I use the term ‘self-documenting’ in the sense that one can attach the documentation directly to the database itself, rather than create a separate document. Basically, you use the extended properties of data objects to apply your own properties to the metadata.
Using extended properties
The classic example is to use extended properties of an object to append a description of that object, be it a trigger, stored procedure, function, table, view, parameter, index, constraint or column. One can also use extended properties to document details such as the date of revision, author, input mask, caption, history, rendering-style, and so on.
One of the classic general tricks that programmers have used in the past to add documentation to source code is to structure the comments of source code by inserting predefined markers to indicate the category of information (revision date, for example) to any application that generates the documentation (such as Javadocs). This can’t be done in SQL Server as source is only maintained in the database for certain objects such as procedures and functions. Since the introduction of extended properties, such tricks would be unnecessary anyway.
The advantage of using the extended properties is that the documentation, notes, and so on stay with the databases and can be used as a “live documentation” mechanism. They are backed up with the database, and scripted out with the build scripts.
Despite their obvious utility, Microsoft has treated the feature with a curious lack of enthusiasm. There is the smell of ‘wet paint’ about the design. Extended properties allow you to document your database objects but it has been left to third-party utilities such as SQL Doc and DBDesc to exploit the use of these properties for generating the full documentation of the database from the database itself.
A consequence of Microsoft’s indifference to extended properties is that they forgot to include them in the replication synchronisation process. You have to do it manually (a tool such as SQL Compare will synchronise them properly). Also, they neglected to provide an Information_Schema view of the extended properties, which would have made to make it easier to access them from SQL.
Another difficulty is that some third-party software vendors have used the extended properties for other purposes, such as storing parameters for entity-relationship diagrams. This makes it difficult for utilities that extract the documentation as there is no standard property name other than
Enterprise Manager / SQL Server Management Studio
Microsoft provides one extended property,
MS_Description, which can be used from both Enterprise Manager and SSMS to provide a description of the object to which it is bound. Further, the Diagram Designer provides a description field, accessible via the custom view, which provides an easy way of viewing and editing the documentation of the columns.
However, extended properties are just about providing basic descriptions of objects. They are a lot more versatile that that. The designers of extended properties sensibly placed no restrictions on the properties that one could attribute to database objects. It is perfectly OK, for example, to provide extra metadata to assist the application layer in rendering or querying the data.