SQL: Fun with Extended Properties in SQL Server 2008

Reference: http://sqlserverperformance.wordpress.com/2011/01/10/fun-with-extended-properties-in-sql-server-2008/

Fun with Extended Properties in SQL Server 2008

Posted on January 10, 2011by Glenn Berry

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.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s