Associating Data Directly with SQL Server Database Objects.

It is easy to attach details and documentation to a SQL Server database using extended properties. In fact, you can add a number of items of information to any database objects such as tables, views, procedures or users. If you use JSON to store the information, then you can, in addition, even monitor trends by storing previous information as a back history of changes. This could include such information as the date of changes, or variables such as the size of the database or table at a particular date, with the object.

I’ll use as an example the applying of version numbers to a database. We’ll end up storing old version numbers and the date when they were applied. We’d want to do this to build up a history of when changes were made to a database. This allows us to find out various items of information as well as the current version: We can, for example, find out when, and how long, a database was at a version number.

Storing a version number for a database in JSON.

Let’s take things in easy steps.

Without a history

Imagine that you have a database called ‘AdventureWorks’ that you need to document. You might have several different facts that you need to store: a description maybe, more likely a version number. There are likely to be other facts you need to document. You might decide to store it a JSON so that you can access just part of the data.

You can now view this in SSMS, of course

You can access it in SQL via various different techniques depending on your preferences.

You can just access the current version information, or any other value, using JSON_VALUE()

You can get the data as a result in various forms, including a single row or one row per key. Let’s first get the JSON value from the extended property …

.. then you can get the data as a result with a single row …

…or you can get the result with one row per key.

This data isn’t entirely secure. You need CONTROL or ALTER permissions on the object to alter it, but it can be accessed by anyone who has VIEW DEFINITION permission. We can demonstrate this now by creating a ‘headless’ user without a login and assigning just that permission. You can try this out with various permissions to see what works!

Storing a history as well

At this point, you probably decide that you really want more than this. What you really need is to be able to keep track of versions and when they happened, something like this ….

(Taken from one of the test-harnesses. You’ll notice that we are doing very rapid CI!). Here, we have a database that we are continually updating but we have kept a record of our old versions, when they happened and who did the alterations.

We can access the history like this

..but it would be better to add in the current version like this

We maintain the current record where it is easy to get to and simply add an array to hold the history information. Our only headache is that we can only hold an NVARCHAR of 3750 characters (7500 of varchar characters) because extended properties are held as SQL_Variants. They need careful handling! This means that if our JSON data is larger, we have to trim off array elements that make the JSON exceed that number.

There is an error in the JSON_MODIFY() function that means that it doesn’t actually delete an array element, but merely assigns it to NULL. This can only be rectified by removing the NULL because one generally removes the oldest members of an array that you just append to by deleting element[0]. If it is NULL it still exists. Doh!

Once we have this up and running, there is a way of storing all sorts of ring-buffer information for reporting purposes that is in sorted order. Yes, you’re right, you now have a way of estimating database or table growth and performing a host of other monitoring tasks.

Because the code is rather more complicated, we’ll use a stored procedure. I’m making this a temporary procedure because I like to keep ‘utility’ code away from database code.

The way that this works is that you only need to put in the name of the database and the description first time around, or after you’ve deleted it.

Here is how you delete it.

The following code be necessary the first time around, especially if you’ve used different defaults for your temporary stored procedure.

From then on, it is just a matter of providing the version number

Testing it out

Here is one of the test routines that I used for the stored procedure, but without the checks on the version number, as that would be repetition.


Extended properties are useful for development work but wonderful for reporting on a database or monitoring a trend. If you use JSON to store the data, they can act as miniature tables, or more correctly ring buffers. They have a variety of uses and are easy to create and remove without making any changes to the database whatsoever. This is because Extended properties and their values are either ignored as changes by humans and deployment/comparison tools, or you can easily configure them to be ignored: They do not affect the version)