Most technical professionals don’t seem to enjoy writing and maintaining documentation for their databases. I know that documentation falls pretty low on my favorites list.

One pretty easy way to include some “built-in” database documentation for your databases (which could be the beginnings of a data dictionary) is to add extended properties on your objects. In this case, I want to add an extended property called MS_Description to each user table, which will hold a short description of the table.

Extended properties are exposed in the SQL Server Management Studio (SSMS) GUI, so if you are a glutton for punishment, you can do lots of clicking and typing.

You can also call one of three system stored procedures to manipulate your extended properties. These are:

sys.sp_dropextendedproperty

sys.sp_addextendedproperty

sys.sp_updateextendedproperty

Unfortunately, if you try to drop or update an extended property that does not exist, you will get an error. You will also get an error if you try to add an extended property that already does exist, so you need to be careful.

I wrote a quick and dirty routine that tries to make this process a little more reliable. My code block also shows some examples of how to call the system stored procedures, and how to query the extended properties in your database.

Of course for this to actually be valuable, you actually have to add the extended property to each table (along with anything else you want to add), and maintain it over time. You can also document the columns in your tables, and many other objects in your databases.

The real fun with extended properties is the fact that it does matter how you add them when the property is MS_Description.

There are 3 ways. 1st: Manually, on SQL Server Form, 2nd: Scripting: the one you just have described and 3rd: manually on design view of table (column descriptions).

Now the funny thing is that when you add a column description, it is an MS_Description property created.
When an MS_Description property is created for the column by script, you will not see it on the table design view and if you try to enter something there it results in error, as there is already a property added…
How can SQL server behave differently, when it is the same property?

Also, 3rd party applications sometimes can’t see column descriptions unless they are entered through the table design view….