Documenting SQL Server 2005/2008 Databases

The power of TechWriter comes from its ability to leverage information from your database. In this topic we will show you how to document SQL Server 2005 and 2008 Databases using SQL Server Management Studio to include comments directly within the database itself. Comments can include simple text descriptions or richly formatted content depending on the comment model you wish to use.

Documenting the Database

To create database comments using SQL Server Management Studio, right click on the database and select 'Properties' from the pop-up menu. This will display the Properties dialog similar to the following:

Database Properties

Select the 'Extended Properties' page and add/update a property with the name MS_Description that contains the description for your database.

Documenting Tables

To create table comments using SQL Server Management Studio, right click on the table and select 'Design' from the pop-up menu. This will display the Properties dialog similar to the following on the right side pane:

Table Properties

Creating table comments is as simple as entering text in the description box.

Documenting Table Columns

To document columns using SQL Server Management Studio, right click on the table and select 'Design Table' from the pop-up menu. Next, select the column in the design grid. This will display a Column Properties tab below the table design grid similar to the following:

Column Properties

Documenting your columns is as simple as entering the text in the description box.

Documenting Foreign Key Relationships

To document foreign key relationships using SQL Server Management Studio, right click on the foreign key and select 'Modify' from the pop-up menu. This will display the following dialog:

Foreign Key Relationship properties

Documenting your relationship is as simple as entering text in the description box.

Document Indexes

To document indexes using SQL Server Management Studio, right click on the table and select 'Design Table' from the pop-up menu. Next, click on the 'Manage Indexes and Keys' toolbar. This will display the following dialog:

Documenting your index is as simple as selecting the index you wish to document on the left and entering text in the description box on the right.

Documenting Check Constraints

To document check constraints using SQL Server Management Studio, right click on the constraint and select 'Modify' from the pop-up menu. This will display the following dialog:

Check Constraints

Documenting your check constraint is as simple as entering text in the description box.

Documenting Views

To create view descriptions using SQL Server Management Studio, right click on the view and select 'Design' from the pop-up menu. This will display the Properties dialog similar to the following on the right side pane:

View Properties

Creating view comments is as simple as entering text in the description box.

Documenting View Columns

TechWriter will automatically use the description for the underlying table column as a description provided the view column is not a computed column. To document the computed columns in the view using SQL Server Management Studio, expand the column list for the view in the Object Explorer and right click on the column and select 'Properties' from the pop-up menu. This will display the Column Properties dialog similar to the following:

View Column Properties

Select the 'Extended Properties' page and add/update a property with the name MS_Description that contains the description for your computed view column.

Documenting Functions & Stored Procedures

To document functions and stored procedures using SQL Server Management Studio, right click on the function or procedure in the Object Explorer and select 'Properties' from the pop-up menu. This will display a property dialog similar to the following:

Procedure Properties

Select the 'Extended Properties' page and add/update a property with the name MS_Description that contains the description for your function or stored procedure.

Documenting Function & Stored Procedures Parameters

SQL Server does not currently provide a mechanism for adding descriptions to function and stored procedure parameters. To workaround this limitation, TechWriter supports the use of comment tags for documenting parameters within the description of the function or stored procedure. For example, the JavaDoc param tag can be used to include parameter descriptions as shown below.

JavaDoc Tags

In order to use comment tags, you must select the proper comment model for your database from the Database Settings dialog that is displayed when you add or edit a database for the project.

Formatting Options

In some instances, you may want to format your descriptions with multiple paragraphs, lists, links and other formatting options that are available when using TechWriter's Annotation editor. While SQL Server Management Studio does not provide a WYSIWYG editor, you can still apply formatting to the text using the syntax defined by the comment model. TechWriter supports several different comment models, including HTML, that can be used within SQL Server Management Studio to format your descriptions. See Supported Comment Models for more information on using comment models.