Documenting MS SQL Server Databases

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:

   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:

   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:

   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:

   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:

   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:

   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:

   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:

   • sp_updateextendedproperty
   • sp_dropextendedproperty

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:

   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:

   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:

   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:

   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:

   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:

   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:

   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:

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:

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:

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

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

Next, select the SRV database:

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

The remaining settings should be left enabled:

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

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:

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:

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:

Generating Database Documentation

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

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:

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:

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

• 9^th July, 2019: Initial version