Click here to Skip to main content
15,441,020 members
Articles / Database Development
Article
Posted 9 Jul 2019

Tagged as

Stats

11.6K views
19 bookmarked

Documenting MS SQL Server Databases

Rate me:
Please Sign up or sign in to vote.
4.75/5 (6 votes)
27 Jun 2021CPOL8 min read
The examples of SQL Server database documentation using both the extended properties and a third-party utility.

Introduction

All companies accumulate various data, and this data will inevitably end up stored in a database. Nowadays, almost any application you can think of will contain a database. That is why there is a rather high need in database object description.

There are two main ways in which this process can be approached – you can either use the extended properties of objects or various third-party utilities.

In this article, we’ll review some examples of SQL Server database documentation using both the extended properties and a third-party utility.

How to Document SQL Server Database Using a Documentation Tool

You can add extended properties by using the sp_addextendedproperty system stored procedure. Its documentation is located here.

Here are some examples of how this stored procedure can be used:

  1. A description is added for the dbo.GetPlansObject function’s @ObjectID parameter:
    SQL
    SELECT emp.[EmployeeID]
       ,emp.[LastName]
       ,emp.[FirstName]
       ,s.[SkillName]
       ,DATEDIFF(DAY, jh.[StartDate], _
        jh.[FinishDate]) / (DATEDIFF(YEAR, jh.[StartDate], _
        jh.[FinishDate]) + 1) AS [PeriodDay]
       ,CASE
    WHEN ((2017 >= year(jh.[StartDate])) AND
    (2018 > year(jh.[FinishDate]))) THEN 2017
    WHEN ((2018 >= year(jh.[StartDate])) AND
    (2019 > year(jh.[FinishDate]))) THEN 2018
    WHEN ((2019 >= year(jh.[StartDate])) AND
    (2020 > COALESCE(year(jh.[FinishDate]), year(GetDate())))) THEN 2019
    END AS [Year]
    FROM [Employee] AS emp
    INNER JOIN [JobHistory] AS jh
    ON emp.[EmployeeID] = jh.[EmployeeID]
    INNER JOIN [Project] AS p
    ON p.[ProjectID] = jh.[ProjectID]
    INNER JOIN [ProjectSkill] AS ps
    ON p.[ProjectID] = ps.[ProjectID]
    INNER JOIN [Skill] AS s
    ON s.[SkillID] = ps.[SkillID]
    WHERE (jh.[FinishDate] >= DATEADD(YEAR, -3, GetDate())
    OR (jh.[FinishDate] IS NULL));

    Similarly, you can add descriptions for stored procedure parameters.

  2. A description is added for the dbo.GetPlansObject function:
    SQL
    EXEC sys.sp_addextendedproperty @name=N'MS_Description',
    
    @value=N'Returns all plans for the specified object',
    @level0type=N'SCHEMA',
    @level0name=N'dbo',
    @level1type=N'FUNCTION',
    @level1name=N'GetPlansObject';

    You can add a description for stored procedures and triggers in a similar way.

  3. Add a description for the inf.vColumnTableDescription view:
    SQL
    EXEC sys.sp_addextendedproperty @name=N'MS_Description',
    
    @value=N'Description of table columns',
    @level0type=N'SCHEMA',
    @level0name=N'inf',
    @level1type=N'VIEW',
    @level1name=N'vColumnTableDescription';

    You can also add descriptions for tables.

  4. Add a description for the dbo.TABLE table’s TEST_GUID column:
    SQL
    EXEC sys.sp_addextendedproperty @name=N'MS_Description',
    
    @value=N'Record ID (global)',
    @level0type=N'SCHEMA',
    @level0name=N'dbo',
    @level1type=N'TABLE',
    @level1name=N'TEST',
    @level2type=N'COLUMN',
    @level2name=N'TEST_GUID';

    Descriptions for table columns can also be added.

  5. Add a description for the rep scheme:
    SQL
    EXEC sys.sp_addextendedproperty @name=N'MS_Description',
    
    @value=N'The rep schema objects contain information for reports' ,
    @level0type=N'SCHEMA',
    @level0name=N'rep';
  6. Adding a database description:
    SQL
    EXEC sys.sp_addextendedproperty @name=N'MS_Description',
    
    @value=N'Administration Database
    Version for MS SQL Server 2016-2017 
    (MS SQL Server 2012-2014 is also fully or partially supported).
    Support for all versions up to MS SQL Server 2012 
    may not be at a sufficient level for use in a production environment';
  7. Adding a description for a database index:
    SQL
    EXEC sys.sp_addextendedproperty @name=N'MS_Description',
    								@value=N'cluster index' ,
    								@level0type=N'SCHEMA',
    								@level0name=N'srv',
    								@level1type=N'TABLE',
    								@level1name=N'Recipient',
    								@level2type=N'INDEX',
    	 @level2name=N'indInsertUTCDate';

    Similarly, you can add a description for a view index.

    In order to change or delete a description, you will only need to use the sp_updateextendedproperty and sp_dropextendedproperty stored procedures, respectively. You can read more about these stored procedures in the documentation:

Now, we will analyze the ways in which you can obtain information about object descriptions:

  1. to get information about the description of database objects, you can use the following query:
    SQL
    select
    
    SCHEMA_NAME(obj.[schema_id]) as SchemaName
    ,QUOTENAME(object_schema_name(obj.[object_id]))+'.'+quotename(obj.[name]) as ObjectName
    ,obj.[type] as [Type]
    ,obj.[type_desc] as [TypeDesc]
    ,ep.[value] as ObjectDescription
    from sys.objects as obj
    left outer join sys.extended_properties as ep on obj.[object_id]=ep.[major_id]
    											 and ep.[minor_id]=0
    											 and ep.[name]='MS_Description'
    where obj.[is_ms_shipped]=0
    and obj.[parent_object_id]=0

    The following system views are used here:

    1. sys.objects — database objects. You can learn more about it here.
    2. sys.extended_properties — extended properties in the current database. More details here.

    This query yields the following columns:

    1. SchemaName — object schema
    2. ObjectName — the name of the object
    3. Type — object type
    4. TypeDesc — description of the object type
    5. ObjectDescription — custom object description
  2. to get a description of objects that have parents, you can use the following query:
    SQL
    select
    
    SCHEMA_NAME(obj.[schema_id]) as SchemaName
    ,QUOTENAME(object_schema_name(obj.[parent_object_id]))+_
    '.'+quotename(object_name(obj.[parent_object_id])) as ParentObjectName
    ,QUOTENAME(object_schema_name(obj.[object_id]))+'.'+quotename(obj.[name]) as ObjectName
    ,obj.[type] as [Type]
    ,obj.[type_desc] as [TypeDesc]
    ,ep.[value] as ObjectDescription
    from sys.all_objects as obj
    left outer join sys.extended_properties as ep on obj.[parent_object_id]=ep.[major_id]
    											 and ep.[minor_id]=obj.[object_id]
    											 and ep.[name]='MS_Description'
    where obj.[is_ms_shipped]=0
    and obj.[parent_object_id]<>0

    Here, columns similar to those from the previous query are displayed, but a new column is added. ParentObjectName is the object's parent (for example, the table is the column's parent).

    This query also uses the sys.all_objects system view which shows all database objects. You can read more about this view here.

  3. You can get parameter descriptions by executing the following query:
    SQL
    select
    
    SCHEMA_NAME(obj.[schema_id]) as SchemaName
    ,QUOTENAME(object_schema_name(obj.[object_id]))+_
    '.'+quotename(object_name(obj.[object_id])) as ParentObjectName
    ,p.[name] as ParameterName
    ,obj.[type] as [Type]
    ,obj.[type_desc] as [TypeDesc]
    ,ep.[value] as ParameterDescription
    from sys.parameters as p
    inner join sys.objects as obj on p.[object_id]=obj.[object_id]
    left outer join sys.extended_properties as ep on obj.[object_id]=ep.[major_id]
    											 and ep.[minor_id]=p.[parameter_id]
    											 and ep.[name]='MS_Description'
    where obj.[is_ms_shipped]=0

    Here, the displayed fields are similar to the ones from the previous query. However, there are a number of changes and additions:

    1. Type and TypeDesc — are related to the parent object (stored procedure or function)
    2. ParameterName — the name of the parameter

    This query also uses the sys.parameters system view which shows database object parameters. For a more detailed description, feel free to refer to the view’s documentation.

  4. Descriptions of table column can be obtained with the following query:
    SQL
    select
    
    SCHEMA_NAME(t.schema_id) as SchemaName
    ,QUOTENAME(object_schema_name(t.[object_id]))+'.'+quotename(t.[name]) as TableName
    ,c.[name] as ColumnName
    ,ep.[value] as ColumnDescription
    from sys.tables as t
    inner join sys.columns as c on c.[object_id]=t.[object_id]
    left outer join sys.extended_properties as ep on t.[object_id]=ep.[major_id]
    											 and ep.[minor_id]=c.[column_id]
    											 and ep.[name]='MS_Description'
    where t.[is_ms_shipped]=0;

    The following columns are displayed here:

    1. SchemaName - the name of the table schema
    2. TableName - the name of the table
    3. ColumnName - the name of the table column
    4. ColumnDescription - description of the table column

    The query also uses the following system views:

    1. sys.tables - tables. More details here
    2. sys.columns – columns. Find more info here
  5. Descriptions of view columns can be retrieved by the following query:
    SQL
    select
    
    SCHEMA_NAME(t.schema_id) as SchemaName
    ,QUOTENAME(object_schema_name(t.[object_id]))+'.'+quotename(t.[name]) as ViewName
    ,c.[name] as ColumnName
    ,ep.[value] as ColumnDescription
    from sys.views as t
    inner join sys.columns as c on c.[object_id]=t.[object_id]
    left outer join sys.extended_properties as ep on t.[object_id]=ep.[major_id]
    											 and ep.[minor_id]=c.[column_id]
    											 and ep.[name]='MS_Description'
    where t.[is_ms_shipped]=0;

    The following columns are displayed here:

    1. SchemaName - name of the presentation scheme
    2. ViewName — name of the view
    3. ColumnName — name of the view column
    4. ColumnDescription - description of the view column

    The request also uses the sys.views system view, which displays all database views. You can find more info here.

  6. You can get database schema descriptions by using the following query:
    SQL
    select
    
    SCHEMA_NAME(t.schema_id) as SchemaName
    ,ep.[value] as SchemaDescription
    from sys.schemas as t
    left outer join sys.extended_properties as ep on t.[schema_id]=ep.[major_id]
    											 and ep.[minor_id]=0
    											 and ep.[name]='MS_Description'

    There are two columns, SchemaName and SchemaDescription, which show the names and descriptions of the schemas, respectively.

    The query also uses the sys.schemas system view, which displays all database schemas. More details.

  7. You can get all extended properties of the indices throughout the database by using the following query:
    SQL
    SELECT SCHEMA_NAME(obj.[schema_id]) as [SchemaName],
    	   obj.[name] as [ObjectName],
    	   ind.[name] as [IndexName],
    	   EP.[name]  as [ExtendedPropertyName],
    	   EP.[value] as [ExtendedPropertyValue]
    FROM sys.extended_properties AS EP
    inner join sys.objects as obj on EP.[major_id]=obj.[object_id]
    inner join sys.indexes as ind on EP.[minor_id]=ind.[index_id]
    WHERE EP.class = 7

    The following columns are displayed here:

    1. SchemaName — name of the object schema
    2. ObjectName — name of the object
    3. IndexName — name of the index
    4. ExtendedPropertyName - name of the extended property
    5. ExtendedPropertyValue - value of the extended property

    The query also uses the sys.indexes system view, which displays all indices within the database. You can find more details here.

Next, we'll look at how database documentation can be approached in another way by using a specialized utility from Devart.

Documenting a Database Using dbForge Documenter for SQL Server

In dbForge Studio for SQL Server, you can create a database documentation project. This functionality is also implemented in dbForge Documenter for SQL Server.

In this example, we will use the SRV database designed for MS SQL Server DBMS maintenance. It’s distributed freely for any purpose, so you can download its source files here: https://github.com/jobgemws/Projects-MS-SQL-Server-DBA/tree/master/SRV.

To create a database documentation project after opening the Studio, click “New Documentation ...” in the Tools menu:

Fig.1 Creating a new documentation for the database

Figure 1. Creating a new documentation for the database

Next, select the required servers for which you need to create database documentation.

After this, the following documentation homepage will appear. It can be arranged in various ways, including the following:

Database schema

Figure 2. Customizing the homepage

You can start without generating a homepage in the documentation. To do this, simply uncheck the box at the top left of the window.

Please keep in mind that only the elements with enabled checkboxes will be generated.

Next, you will need to configure the generation both within the scope of all selected servers and for each specific server:

Image 3

Figure 3. Setting up the generation of documentation for all selected servers

For example, let’s disable all settings on this page by switching all options to OFF:

Image 4

Figure 4. Setting the generation of documentation for the selected server

Next, select the “User databases” page. Also, for the example’s sake, let’s toggle off the “User databases” option:

Image 5

Figure 5. Setting up the 'User databases' page

Next, select the SRV database:

Image 6

Figure 6. Setting Up the SRV Database Page

Here, we will enter the description of the database and click “Save”.

All changes in the description that are saved by the user will also be saved in the corresponding objects’ advanced properties.

After that, we’ll turn off "Properties", "Options" and "Database Files":

Image 7

Figure 7. Disabling SRV Database Options

The remaining settings should be left enabled:

Image 8

Figure 8. Viewing the enabled SRV database options

Now let's open the SRV database itself and select the “Tables” page:

Image 9

Figure 9. Viewing and editing SRV table descriptions

This page lists tables and their descriptions.

When you’re editing descriptions, two buttons should appear:

  1. Save - save changes
  2. Cancel - cancel changes

Let’s select the dbo.AuditQuery table by clicking on it:

Image 10

Figure 10. Viewing and editing column descriptions for the dbo.AuditQuery table

This page lists the columns of the table along with their descriptions.

Also, you can see table indices being displayed on this page.

When you’re editing descriptions, two buttons should appear:

  1. Save - save changes
  2. Cancel - cancel changes

In addition, this page contains the table definition code, table description, table properties, etc.

You can turn off any individual option, if this is necessary.

The pages for the views in the Views folder, as well as for other database objects, look the same:

Image 11

Figure 11. Database object types

In this way, you can document the following database objects:

  1. Tables and their columns and indices
  2. Views and their columns and indices
  3. Stored procedures and their parameters
  4. Functions and their parameters, both tabular and scalar
  5. DDL Triggers
  6. Users
  7. Roles
  8. Schemas and others

After setting up the documentation, you can save the project by clicking «Save» and selecting the appropriate path and file:

Image 12

Figure 12. Saving the documentation project

Generating Database Documentation

Now, let's take a closer look at the documentation project’s top panel:

Image 13

Figure 13. Viewing the documentation project menu

There are three buttons here:

  1. Add Connection ... - allows you to add new servers for the documentation
  2. Refresh - starts the process of updating information on selected documentation servers
  3. Generate ... - opens the documentation generation settings window.

Click the “Generate ...” button. The documentation generation window itself will be opened:

Image 14

Figure 14. Setting up the documentation generation

Here, you can select many different options, but the following ones are necessary:

  1. format (usually, HTML is chosen by default)
  2. the folder to which the documentation will be generated

Also, the selected settings can be generated as a .bat file by clicking “Save Command Line ...” at the bottom left.

Next, you will need to click the “Generate” button to start the process of generating the documentation itself. When this operation is successfully completed, the following window will appear:

Image 15

Figure 15. Successful completion of the documentation generation

After that, go to the selected documentation directory and open the mail.html file to open the generated SRV database web documentation.

Similarly, it is possible to collect several databases in one documentation, even if they are located on different servers.

Conclusion

We looked at how object descriptions can be created and viewed both through advanced properties and with the help of dbForge Documenter for SQL Server. Descriptions constitute the bulk of database documentation. Also, from the example shown in this article, we can see that the dbForge Documenter for SQL Server tool allows you to quickly create and edit descriptions of specific objects and the entire documentation. This can be done both for single or multiple databases – no matter if they’re located on one server or on several different servers.

History

  • 9th July, 2019: Initial version

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Database Developer
Russian Federation Russian Federation
I am interested in everything related to the database and data.
Professionally engaged in MS SQL Server as a developer and administrator.
Soul lies on MS SQL Server

Comments and Discussions

 
QuestionCustom Properties Pin
Member 434730128-Jun-21 3:56
MemberMember 434730128-Jun-21 3:56 
AnswerRe: Custom Properties Pin
jsc4229-Jun-21 6:35
professionaljsc4229-Jun-21 6:35 
GeneralMy vote of 5 Pin
Member 434730128-Jun-21 3:49
MemberMember 434730128-Jun-21 3:49 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.