Introduction

In any good programming reference, you will read that a developer has to document his code, not only for him/herself but also for the person who, ten years later will be asked to maintain it. This would, of course, be made easier thanks to a good documentation of existing code.

But, documentation is not simply some document we can open when we need it. It’s a complete process that should start no later than the end of every development task with other processes, like Code Quality Assessment and Testing. In the best case, both of these processes should take documentation into account. For instance, a unit test that fails whenever an undocumented object is found could be created. If everything is done well, then we are sure that each version of a database solution that is distributed always contains documentation. Note that a unneglectable step of this documentation process should be a regular review of existing documentation so that it stays up-to-date and reflects reality.

The following figure depicts and summarizes development workflow as discussed above.

As SQL Server database development is a good candidate for code documentation, this development workflow applies and this article will focus on following steps:

• Inventory assets to document
• Describe undocumented assets
• Review documented assets

In the following sections, we will first review a solution provided by Microsoft for documenting SQL Server database objects and come to the conclusion that this has some missing features and that we could define another way to document our code.

Then we will discuss a first solution that consists in the implementation of a stored procedure that calls the built-in CRUD components presented above in order to manage extended properties from a single-entry point.

However, this solution won’t overcome some aspects. So, we will review a second solution that will implement steps in development workflow related to documentation.

• The first step, Inventory assets to document, will leverage a corresponding stored procedure that will populate one or more SQL tables based on objects in the INFORMATION_SCHEMA schema and optionally consider existing extended properties.
• The second and third steps are more of a manual process and we will discuss a set of database objects that will become helpful in the completion of this process and the way to chain them.

Finally, as we should not close the door to direct extended property usage, we will review a stored procedure that will take the data back from the documentation we did, using the review process, and apply the documentation to extended properties for each SQL database.

Important Note

Some of the presented procedures are still under development and have only been tested with common SQL Server database objects like tables, views, procedures, types, and functions. Handling of other kinds of objects like those related to SQL Server Service Broker is still to be implemented.

Microsoft’s solution for SQL Server documentation: Extended Properties

Well, SQL Server developers at Microsoft may agree with the principle of documenting database development. This is certainly the reason why we can find a feature called Extended Properties since (at least) SQL Server 2005 version.

You can imagine it as metadata associated with a given database object (and even a database itself). Since the metadata are stored in the database, it are accessible to any database principal with sufficient privileges. Furthermore, it is backed up and restored whenever the database is respectively backed up or restored.

We can define multiple extended properties to a single object. These properties can bear the name you want but there are commonly used names like:

• ‘MS_Description’ that is used by multiple tools as the name for a description property of the object. We will talk about it again later in this article.
• ‘Version’ and ‘VersionDate’ (mainly for database or schema objects)

These extended properties can be accessed using a T-SQL query to sys.extended_properties table.

Here is a sample result of a query that selects every column from that table.

Records in that table can be respectively created, edited or removed using following stored procedures. Their names speak for themselves.

• sys.sp_addextendedproperty
• sys.sp_updateextendedproperty
• sys.sp_dropextendedproperty

There is also a built-in function that allows you to list extended properties based on a series of criteria. This procedure is called sys.fn_listextendedproperty. Those four programmable components will be referred as built-in CRUD components in the remainder of the article.

We could also access these properties using SQL Server Management Studio by right-clicking on a database object and choosing to display its properties. In the dialog, we will find an Extended Properties tab.

While this feature seems very helpful, it’s really underused and there are simple reasons for it:

• Lack of knowledge: developers do not even know it exists
• Lack of time
• Usage of three stored procedures above is not that simple: the add procedure fails when a record exists, the update one when there is no defined extended property
• One database at a time: as extended properties are stored in a database, you can’t query for extended properties from multiple databases in a single SELECT query.
• Properties are dropped when the object is dropped
• etc

Solution 1: a stored procedure to manage extended properties

There are some problems in the list of potential reasons for the lack of adoption of extended properties that can be overcome with the implementation of a stored procedure. For instance, it would be easy to remove the obligation to use multiple objects and check whether an extended property already exists or not.

That the raison d’être of Utils.ManageExtendedProperty stored procedure. This stored procedure will take action of any extended property no matter the database. It will use have almost the same signature as built-in CRUD components for extended events management.

Let’s review its parameters.

First, there is the database name in which our stored procedure should apply modifications. If no one is provided, it will take current database name.

Then, there is the name of the extended property on which we want to operate followed by common level<X>(type|name) parameters of sp_<action>extendedproperty built-in CRUD components.

Finally, we can find @OperationMode and @Debug parameters. First one will allow its user to tell stored procedure which action it has to take for this set of parameters. There are 4 operation modes:

1. OVERWRITE (It’s the default. It creates or replaces value for the extended property)
2. APPEND (reads existing property if any and append provided value for @PropertyValue parameter to it.)
3. PREPEND (reads existing property if any and prepend provided value for @PropertyValue parameter to it.)
4. REMOVE (calls sp_dropextendedproperty)

First three modes will generate a call to sp_addextendedproperty stored procedure whenever parameter set leads to an undefined extended property. In contrast, when an extended property is defined, then it will call sp_updateextendedproperty stored procedure.

Note

1. The creation script for this stored procedure is attached to this article.
2. This stored procedure is not complete and some additional work should be done on it so that it covers all the cases, but it works in all the cases presented here.

While this stored procedure could simply make the call, it does extra checks like:

• Checking target schema exists
• Checking target object exists
• Checking target attribute exists
• …

If one of those checks fails, then an error is raised and no modification is done to values of existing extended properties.

Note

There is also a parameter @_NoChecks which, when set to 1, tells stored procedure not to perform those checks. In that case, all accepted parameter associations that are defined in the stored procedure body will affect corresponding extended properties. No extensive tests have been made with this mode but it should work

In contrast, when @_NoChecks default value (0) is used, it will take into account only extended properties corresponding to accepted parameters associations, for which additional checks are defined and which corresponding values passed those checks.

The algorithm of this stored procedure is quite simple:

1. Check parameters associations validity
2. (Optional) check underlying objects exists
3. Get extended properties if any
4. (Optional) Append or prepend the extended property value
5. Take action
   • Delete extended property if @OperationMode is set to ‘REMOVE’ and an extended property exists
   • Create an extended property if no one exists
   • Update the extended property if it already exists

Here is an example call to this procedure in the context of documenting a function called Testing.GetDocTypeCount in TestSchemaDoc database.

Solution 2: homemade documentation solution

As solution 1 does not overcome all the limitations of Extended Properties, we will design our own solution.

In following, we will first review different objects that are core components of the implementation of the homemade documentation solution. Basically, they are tables or views. Then, we will review the 3 steps in development workflow that are considered in this article.

Core objects overview

Except when stated differently, all objects are in a schema called [Docs]. There are two tables:

• SchemaObjectDictionary which is responsible to keep track of objects defined in a database. All objects that come with SQL Server won’t be part of records for this table.
• SchemaObjectsAttributes which will keep records of columns defined for either tables or views so as routines (procedure/functions) related to a record of former table.

Here is the relationship between those tables:

Note

• As this documentation could be externalized, we already added a ServerId column which will be NULL on a single SQL Server Instance. This column could be removed from all code here if you don’t want it.
• You could notice the hasBeenDeleted column. With this, documentation can persist an object or attribute deletion. Documentation manager should define the way these records should be managed.

We will create CRUD stored procedure for each table. This stored procedure will be attached to this article and will be called using the following naming convention:

Where <Action> is either:

• Insert
• Update
• Delete
• Upsert (insert or update)

As SchemaObjectsAttributes table only contains a reference to a schema object definition in SchemaObjectDictionary table, we will create a view that will join both tables. This view is called SchemaObjectsAttributesDetails and the query use to define it is as follows:

Here is a sample output for this query:

As final objective is to document every SQL Server object inside a given database, we will also create a view that we will call SchemaObjectDictionaryMissingDescription. This view will return a list of objects with missing documentation either about itself or about one or more of its columns.

If possible, we could generate a T-SQL statement. We would edit this statement in order to update record and make it disappear from records returned by that view. This goal will be achieved by DDL2UpdateDescription column. We could be willing to get details about related objects (or columns). This operation will be available using DDL2GetDependentDescription column.

In terms of implementation, DDL2UpdateDescription column will contain a call to SchemaObjectDictionary_Upsert procedure that runs an INSERT or UPDATE statement like a MERGE would do while DDL2GetDependentDescription column will call a stored procedure called GetRelatedObjectDescription that will query SchemaObjectsAttributes table to get back attributes and their description of a particular object. The creation script for this stored procedure is also attached to this article.

Implementation of “Inventory assets to document” step

Considerations

Now that the core tables are defined, we still need to populate them. We could do it manually and get used to creating a record each time we create a new table but how would we do for already existing systems?

We will need to create a stored procedure that will do the job for us. Let’s call it PopulateSchemaObjectDictionary. In order to test it and also to get an overview of extended properties, we will define a parameter called @ReadonlyExecution so that, when set to 1, will tell stored procedure not to update dictionary but to display lookup results instead.

Moreover, we might know that a lot of tools with extended features, like ApexSQL Doc, that help a developer to document its SQL Server database objects use an extended property called MS_Description.

As we will consider all objects inside the database, we should also consider getting back existing values from these extended properties. By the way, we should let the door open to the adoption of another standard and let user the capability to change the name of the extended property to consider.

Finally, as an application is not always limited to a single database, our stored procedure will run by default against all user databases except if a value for a DatabaseName parameter is provided. We also added a filter @SchemaName to take only objects from that schema name.

This will give us following signature for PopulateSchemaObjectDictionary stored procedure:

Implementation details

The code for this procedure is provided with this article. We will review some details in its implementation.

It’s a three-step process that is summarized with following diagram:

Initializations and temporary tables creation

This procedure will first create two temporary tables #DiscoveredSchemaObjects and #DiscoveredDataObjectsProperties that are similar to both tables discussed above.

Temporary tables population

Then it will populate #DiscoveredSchemaObjects temporary table using information from following tables or views in each database:

This will constitute a good starting point but should be extended in the future with sequences and other kinds of database objects.

Note

The loop that runs the query to get back information has been replaced by Common.RunQueryAcrossDatabases stored procedure that will also be attached to this article but can to be replaced by either sp_MSForEachDB or another piece of T-SQL code.

The population of #DiscoveredDataObjectsProperties temporary table will be performed by following query. This query will be run against all databases of interest.

Then the stored procedure will take @ReadFromExtendedProperties and @ExtendedPropertyName parameter values and eventually query sys.extended_properties table in each database of interest in order to update description for collected objects and columns/parameters.

Finally, based on the value of @ReadonlyExecution parameter, we will either return contents of both temporary tables (#DiscoveredSchemaObjects and #DiscoveredDataObjectsProperties) or update dictionary tables (SchemaObjectDictionary and SchemaObjectsAttributes).

Actual dictionary update

Actual dictionary update is performed using two MERGE statements. Data source for these statements are temporary tables discussed above.

Calling the stored procedure

Following code will call population stored procedure for current database.

As we are in Debug mode, we can scroll down in the Messages part of SSMS and we will get a report on the number of objects it found.

Here is a sample of first collection.

Note

Only 17 objects are displayed and we got 26 objects reported to have been inserted. This is due to the fact that I activated database diagrams and I got a set of objects from dbo schema related to this feature of SSMS. These tables are not shipped with SQL Server.

Documentation review (the other steps)

Well, now let’s say we have populated core tables and we are at step 2 which, as a reminder, is entitled “Describe undocumented assets”.

To do so, we will call Docs.SchemaObjectDictionaryMissingDescription. This view will provide us a list with objects that has either not description for themselves or for one or more of their attributes.

Here is a sample results of a query to this view to get back objects from dbo schema.

There are two columns we need to look at. First one is the MissingDescription column. If its value is 1, then we need to document the SQL Server object itself. This can be performed easily by editing and running the statement we get back from DDL2UpdateDescription column.

Here is the value taken from second row in previous example.

The second column to look at is DependentMissingDescriptionCount. If its value is bigger than 0, then we should copy and execute the statement from DDL2GetDependentDescription column value.

For second situation, we have to run the T-SQL statement in DDL2GetDependentDescription column. Here is the value for that column still from second row with its results.

If we move our horizontal cursor to the right, we will see the UpdateDML column. Those attributes without description can be documented using a modified version of the value from this column like we did for DDL2UpdateDescription column.

The review of already documented objects and attributes should be performed on regular basis but could be oriented based on the objects that were modified during development task. We could also add following columns and base our process on the value of these columns:

• LastReviewDate would store date and time of last review of the asset
• LastReviewVersion would store last version when the asset has been reviewed
• LastReviewComments would store optional comments of the review

We could only display attributes that have not been reviewed since for instance 6 months or 10 versions.

Copying documentation to Extended Properties

Once we’ve completed documentation review steps, we could optionally apply this documentation to Extended Properties. Why? Because, as explained above, there are many tools (free or not) that use the MS_Description extended property as input to generate a read-only (or editable) version of your documentation in a transmittable format like HTML. Furthermore, SSMS users can access these properties and quickly get valuable information.

To do so, we will create a stored procedure called that will look at records from SchemaObjectDictionary and SchemaObjectsAttributes tables and call previously discussed Utils.ManageExtendedProperty stored procedure. We will call this new procedure [Docs].[ApplyDictionaryDefinitionsToExtendedProperties].

The implementation of such a procedure is pretty trivial. It will store in a temporary table mandatory information for calls to Utils.ManageExtendedProperty stored procedure to succeed. This means it will scan for records that do not have a HasBeenDeleted column set to 1 and have a non-empty ObjectDescription or AttributeDescription column.

This is done using following query

Then it will loop on results from former query to call Utils.ManageExtendedProperty stored procedure.

Setup of documentation solution

Attached to this article, you will find multiple files. They should be executed following this order:

1. CreateSchemas.sql
2. Table.Docs.SchemaObjectDictionary.sql
3. Procedure.Docs.SchemaObjectDictionary_CRUD.sql
4. Table.Docs.SchemaObjectsAttributes.sql
5. Procedure.Docs.SchemaObjectsAttributes_CRUD.sql
6. View.Docs.SchemaObjectDictionaryMissingDescription.sql
7. View.Docs.SchemaObjectsAttributesDetails.sql
8. Procedure.Utils.ManageExtendedProperty.sql
9. Procedure.Docs.GetRelatedObjectDescriptions.sql
10. Type.Common.DatabaseNameList.sql
11. Procedure.Common.RunQueryAcrossDatabases.sql
12. Procedure.Docs.PopulateSchemaObjectDictionary.sql
13. Procedure.Docs.ApplyDictionaryDefinitionsToExtendedProperties.sql

This order has been tested and was successful on a SQL Server 2012 instance.

Summary with a concrete example using second solution

Testing object management

Let’s create a table with defined extended property and check that it’s well added in dictionary tables.

Let’s now add a description to key value table:

And also to columns:

Let’s see what will happen if we run once again following query:

We see that it detected the new table as we get a report with one object found in the database (Sorry for the stored procedure not to be good at grammar).

And if we check in tables, we can see that comments have already been added in ObjectDescription column:

Now, let’s say we rename the KeyValue column to Value4Key using following T-SQL statement.

Notice that we get a warning message:

Let’s run again the population procedure and see what will happen…

First, as expected, nothing has changed for the record describing table, but changes are visible in SchemaObjectsAttributes as you can see below:

We see that record corresponding to KeyValue column has been marked as deleted and that a new record corresponding to Value4Key has been added. Now it’s up to you to decide whether to complete ReplacementDefinitionId column for deleted record or simply delete the row.

If we look at extended properties corresponding to the table, we get this:

Now let’s drop the table.

If we run again a query against sys.extended_properties table, we’ll get no results, meaning that an accidental drop of that table would lead to the loss of documentation about that table too.

Let’s see what happens when we run again the population stored procedure… First of all, it did not detect the table KeyValueTbl table as it has been dropped.

But we haven’t lost documentation so far: we can see that everything has been marked as deleted but it’s up to the user to decide the appropriate action.

Testing SQL Server object documentation

Now let’s say that we want to document the test SQL database in which we ran queries so far. To do so, we will first run population stored procedure without value for its @SchemaName parameter, then we will use the SchemaObjectDictionaryMissingDescription view.

The previous figure is a sample screen capture with first line returned by previous view corresponding to RunQueryAcrossDatabases stored procedure. We see that the object has a missing description so as eight of its parameters.

Here is an example of value for DDL2UpdateDescription column:

As we can see, everything is ready to update record. We just need to change TODO: YOUR_DESCRIPTION with something else.

Let’s say we modify previous statement to the following one:

We should do the same for every object appearing with MissingDescription column with a value of 1.

By the way, if an object is still in the list with a value of 0 for that column, it means that we should consider DDL2GetDependentDescription column.

You will find below the value of that column for RunQueryAcrossDatabases stored procedure.

Here is a sample result of what we get when we run the previous statement:

As you can see, there are not only details we can get from SchemaObjectsAttributesDetails view but also an UpdateDML column. This column contains, for each record, an almost ready to use call to a DataObjectColumnDictionary_Upsert stored procedure as shown in following example:

Let’s modify previous statement to following one and run it.

Once it’s done, let’s run again the query against SchemaObjectDictionaryMissingDescription view and we can see that the DependentMissingDescriptionCount has a value of 7 instead of 8 previously.

Final word

In this article, we’ve seen that we could use a built-in documentation feature called Extended Properties and we even extended it with a stored procedure. While this feature seems good, there are problems to it like the persistence of documentation after object deletion. That’s the reason why we defined a homemade solution we could use in common cases (some additional work should be done to consider all possible database objects) and that can interact with Extended Properties so that 3rd-party tools can also use our documentation.

Downloads

• Article scripts

References

• sp_addextendedproperty (Transact-SQL)
• System Information Schema Views (Transact-SQL)
• How do you document your databases?
• Using Extended Properties on Database Objects
• Viewing Extended Properties

• Author
• Recent Posts

Jefferson Elias

Living in Belgium, I obtained a master degree in Computer Sciences in 2011 at the University of Liege.

I'm one of the rare guys out there who started to work as a DBA immediately after his graduation. So, I work at the university hospital of Liege since 2011. Initially involved in Oracle Database administration (which are still under my charge), I had the opportunity to learn and manage SQL Server instances in 2013. Since 2013, I've learned a lot about SQL Server in administration and development.

I like the job of DBA because you need to have a general knowledge in every field of IT. That's the reason why I won't stop learning (and share) the products of my learnings.

View all posts by Jefferson Elias

Latest posts by Jefferson Elias (see all)

• How to perform a performance test against a SQL Server instance - September 14, 2018
• Concurrency problems – theory and experimentation in SQL Server - July 24, 2018
• How to link two SQL Server instances with Kerberos - July 5, 2018