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 up with:
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.

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

Use options:

• 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, always containing the actual description of the database structure.

An example of a report with information about a table:

')

Functional 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 with detailed information about the object will be opened.
   
2. Table Details Report - detailed information about the table:
   • Description of the table.
   • Column names, their type, length, and nullable attribute.
   • Description of the table constraints.
   • Description of the indexes of the table.
   • List of tables referencing this table by foreign key
3. View Details Report - view details
   • Description of the presentation.
   • The names of the columns, their type, length.
   • Presentation text.
4. Stored Procedure Details Report - detailed information about the stored procedure:
   • Description of the stored procedure.
   • The names of the parameters, their type and length.
   • The text of the stored procedure.

Installation

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

SQL procedures for documenting 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 to work with the following objects:

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

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



Those. instead of sp_XXXextendedproperty , you can now use:

Plans

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