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