| This article describes how the improvements in Microsoft's SQL Server
2005 relational database allow for most database entities to be documented. The
documentation is held within the database itself, allowing the documentation to be kept
synchronised with the database itself. These enhancements offer the possibility of a
self-documenting database, saving many hours of DBA and developer time.
Table Column Descriptions
In SQL Server 2000, it is possible to add a text description of a table column by using
the SQL Server Enterprise Manager. The table column description is added by selecting the
appropriate table, selecting Design Table, then entering the column's description in the Description
field, as shown below:
Figure 1. Adding a table column description via the SQL Server Enterprise Manager
As such, this feature makes it straightforward for application developers and database
administrators to keep a record of what a table column is used for, plus any other useful
information, such as recording if a field is no longer in general use, or showing if the
column is a foreign key to another field.
In SQL Server 2005, the SQL Server Management Studio database management
utility also allows table column descriptions to be added to database tables. The column
descriptions are added by selecting the appropriate table column, selecting Properties,
then when the Column Properties window appears, selecting Extended Properties from this
Window. The table column description is listed in properties as MS_Description:
Figure 2. Adding a table column description via SQL Server 2005's SQL Server Management
Studio
Along with table columns, many other database entities can be documented
in SQL Server 2005. These include:
- Tables.
- Views.
- Stored Procedures.
- Functions.
- The database itself.
The descriptions for these entities can be added to the database by editing that
entity's MS_Description extended property. Since the description is held within
the database itself, it makes it much more straightforward to synchronise a database with
its documentation.
Creating Database Technical Documentation
Asides from creating database diagrams, there still isn't a lot of functionality within
SQL Server 2005 that allows you to create technical documentation for your database.
Fortunately there are timesaving 3rd party SQL Server documentor tools that will
automatically create comprehensive technical documentation for SQL Server databases. Our SQL Documentation Tool is a low cost option
for quickly creating technical documentation for SQL Server databases with the minimum of
effort. It supports SQL Server 2005's database extended properties, allowing them to be
retrieved from the database and automatically included in the resulting technical
documentation. For example, the sample screenshot below shows SQL Documentation Tool
created documentation for the Address table in AdventureWorks database, complete with
table and table column descriptions:
Figure 3. The SQL Documentation Tool incorporates database descriptions in the technical
documentation, resulting in high quality database documentation that is straightforward to
keep synchronised with the database itself.
Further Reading
|
