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

    Database Objects Report
    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:
    Table details report

    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.


    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 :

    sp_addextendedproperty / sp_updateextendedproperty

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



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

    Also popular now: