Worked example: documenting two databases - SQL Doc

This worked example demonstrates how to generate database documentation for objects in two SQL Server databases.

In this example, the Magic Widget Company runs a SQL Server instance to support their website and company activities. This instance contains two databases. The first database contains the company's product and customer information. It is primarily accessed by the company's intranet. The second database contains customers' credit card information, and access to this database is controlled. The Magic Widget Company's development team have been asked to document these databases.

You can follow the example on your own system. You will need access to a SQL Server to do this. Note that this example uses SQL Server 2008; if you are using SQL Server 2005 or SQL Server 2000, you can follow the example, but you will see slightly different results.

1. Setting up the databases

The worked example uses the following databases:

WidgetProducts
WidgetCustomers

To create these two databases on your SQL Server:

If they already exist, delete the databases WidgetProducts and WidgetCustomers from your SQL Server.
Click here to view the SQL creation script for the databases.
Copy the script, paste it in your SQL application, and then run it.
The databases and their schema are created.

2. Setting up the documentation project

If you have not yet started SQL Doc, select it from your Start menu; if it is already running, click New Project to open the Project Configuration dialog box.
On the Connection tab, in the Server list, type or select the SQL Server instance for the WidgetProducts and WidgetCustomers databases.
On the Documentation Setup tab, in the Project name box, type Widgets. In the Project description box, type Example project. These details will be included on the project information page of the generated documentation.
For this example, you are going to include the default copyright information in the project information page and footer of the documentation, so the project configuration is now complete. Click OK to save the settings.

3. Viewing the database objects

SQL Doc displays a list of databases for the SQL Server instance in the main window.

To view the objects in the WidgetProducts database, click to expand the database.

To see a list of objects of a particular type, click the object type; to see a summary for a particular object, click to display objects, and then click the object's name. SQL Doc displays a preview of the documentation for the object you select.

For example, display the WidgetCustomers table: the preview shows the table's properties, column details, index details, permissions details, and SQL creation script.

WEGS_ObjSelection_Preview

The preview does not show row count information or object dependencies; these will be included in the generated documentation.

Find out more about what information is included in the documentation

Editing an object description

In the preview, the text at the bottom of the main object box and in the Description column shows the MS_Description extended property values from the database. You can edit this description by clicking

For example on the preview for the WidgetCustomers table, click in the grey box at the top of the preview. In the Edit Object Descriptions dialog box, at the end of the description, type 2008, then click Update Database to save your change. SQL Doc updates the extended property in the WidgetProducts database and refreshes the documentation preview to show your new descriptions.

4. Selecting the databases and objects to document

To include objects in the database documentation, you select the objects using the appropriate check boxes.

For this worked example, we will select all the objects in the WidgetProducts database and only the sp_getcardforcustomer and sp_getnamesforcard stored procedures in the WidgetCustomers database.

Select the check boxes for the dbo.sp_getcardforcustomer and dbo.sp_getnamesforcard stored procedures.
Select the check box next to the WidgetProducts database; all the objects in the database are also selected.

You can select objects using the arrow keys on your keyboard. For example, use the Up Arrow key to move up the list so that Tables is highlighted, under WidgetCustomers. Press Right Arrow to expand Tables and then press Down Arrow to highlight dbo.CustomerCards. To select the object, press Space; to clear the selection, press Space.

5. Generating the documentation

The documentation project is now set up, and you can generate the documentation.

Click Generate Documentation.
On the Generate Documentation dialog box, in the Save in box, specify the location where you want SQL Doc to create the documentation.
Select a File type. For this example, use the Web page (.html) file type. Select the Use frames check box if it is not already selected.
Click Generate Documentation to start generating the database documentation.
Click OK to close the message box.
Click Save Project on the SQL Doc toolbar to save the project. The project configuration, object-selection, and generation settings are stored so they are available next time you open the project.