Click here to Skip to main content
14,236,176 members

Documenting MS SQL Server Databases

Rate this:
4.60 (3 votes)
Please Sign up or sign in to vote.
4.60 (3 votes)
9 Jul 2019CPOL
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 documente 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:

EXECUTE sp_addextendedproperty @name = N'MS_Description',

@value = N'Object identifier',
@level0type = N'SCHEMA',
@level0name = N'dbo',
@level1type = N'FUNCTION',
@level1name = N'GetPlansObject',
@level2type = N'PARAMETER',
@level2name = N'@ObjectID';

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:

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.1) sys.objects — database objects. You can learn more about it here

1.2) sys.extended_properties — extended properties in the current database. More details here

This query yields the following columns:

1.1) SchemaName — object schema

1.2) ObjectName — the name of the object

1.3) Type — object type

1.4) TypeDesc — description of the object type

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

3.1) Type and TypeDesc — are related to the parent object (stored procedure or function)

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

4.1) SchemaName - the name of the table schema

4.2) TableName - the name of the table

4.3) ColumnName - the name of the table column

4.4) ColumnDescription - description of the table column

The query also uses the following system views:

4.1) sys.tables - tables. More details here

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

5.1) SchemaName - name of the presentation scheme

5.2) ViewName — name of the view

5.3) ColumnName — name of the view column

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

7.1) SchemaName — name of the object schema

7.2) ObjectName — name of the object

7.3) IndexName — name of the index

7.4) ExtendedPropertyName - name of the extended property

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

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

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

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

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

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

Fig.5 Setting up the 'User databases' page

Next, select the SRV database:

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

Fig.7 Disabling SRV Database Options

The remaining settings should be left enabled:

Fig.8 Viewing the enabled SRV database options

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

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

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

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

Fig.12 Saving the documentation project

Generating database documentation

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

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

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

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

 

License

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

Share

About the Author

Evgeniy Gribkov
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

 
-- There are no messages in this forum --
Article
Posted 9 Jul 2019

Stats

1.5K views
10 bookmarked