Creating documentation for MS SQL database

I bring to your attention a small project for a database developer. It began as a study of reporting capabilities for SQL Server Management Studio, and ended as:
Database Documentation Reports , project address: http://DbDocReports.CodePlex.com .


This is a set of reports for MS SQL Management Studio and MS SQL Reporting Services for creating database documentation using descriptions from Extended Properties . Reports contain detailed information about database objects, such as tables, views, and stored procedures.

Supported by SQL Server Management Studio 2005/2008 and SQL Server Reporting Services 2005/2008. Using the report engine allows you to easily export received reports to Excel, Word, PDF, and other formats.

Use cases:

• Quickly view / print information on database objects in a convenient form in Management Studio or Reporting Services.
• Creating database documentation for your project in Word / Excel / PDF format through exporting reports.
• Using reports in Reporting Services as a site that always contains an up-to-date description of the database structure.

Example report with table information:

Functionality Description

The current version ( 0.9 Beta ) contains the following reports and features:

1. Database Objects Report - general information about database objects (their number and a list with a description). If you select any of these objects in the report, the corresponding report will open with detailed information about the object.
   
2. Table Details Report - detailed information about the table:
   • Description of the table.
   • Column names, their type, length, and nullable attribute.
   • Description of table constraints.
   • Description of table indexes.
   • List of tables referencing this table by foreign key
3. View Details Report - view details
   • Description of the view.
   • Names of columns, their type, length.
   • Presentation text.
4. Stored Procedure Details Report - Detailed information about the stored procedure:
   • Description of the stored procedure.
   • Names of parameters, their type and length.
   • The text of the stored procedure.

Installation

Reports are * .rdl files. To connect them to SQL Management Studio, use the Reports / Custom Reports menu (SQL 2005 SP2 / SQL 2008). For Reporting Services should download these files to the server and connect to a data source, or you can use the Report Viewer ( 2005 / 2008 ).
Details of all installation steps are described in the instructions: in Russian / English .

SQL procedures to facilitate the documentation of database objects

In addition, I would like to share procedures for convenient manipulations with the “MS_Description” property for documenting database objects from TSQL. Download SQL script sp_addDescXXX_install.sql .

In the current implementation, stored procedures are written for working with the following objects:

• Tables (columns, restrictions, indexes);
• Views (columns);
• Functions (parameters);
• Procedures (parameters);

This was done in order to facilitate the use of bulky constructions: sp_addextendedproperty / sp_updateextendedproperty :



i.e. instead of sp_XXXextendedproperty , you can now use:

Plans

1. Extend the list of documented objects (schemes, triggers).
2. Error correction.
3. Wishes and suggestions.