THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Michael Coles: Sergeant SQL

SQL Server development, news and information from the front lines

T-SQL Tuesday: Easy Extended Properties

Ahhh, attention to detail. I misread Adam's T-SQL Tuesday rules and posted early. So here I go again :) This time the puzzle is how to add and update metadata via extended properties in the database with minimal headaches.

SQL Server supports a great feature for storing database object metadata in the database in the form of extended properties. The main benefits of extended properties over custom solutions are: (1) extended properties are managed and stored internally by SQL Server; (2) many third-party applications retrieve/use extended properties. Perhaps the best known extended property is the "MS_Description" property, which is set by the "Description" field in the SSMS designer pages (shown below):

Setting MS_Description property in SSMS 

Extended properties are created as user-defined key/value pairs assigned to database objects. You can define the extended propery keys and their associated values to be anything you like, although there are a few commonly used ones (like "MS_Description"). The values are defined as sql_variant data types. Here are some sample key/value pairs:

 

Key

Value

MS_Description

This table represents the culmination of a lifelong dream.

Caption

GrandTotal Column

Last_Update

2009-07-09

Version

1.0.1

Extended properties allow you to store metadata in the database closely tied to database objects. So what's the downside to extended properties? Basically the only real issue with them is the nonintuitive syntax. In order to use extended properties you have to use stored procedures like sp_addextendedproperty. The following stored procedure call sets the MS_Description extended property on the bar column of the dbo.foo table:

EXECUTE sys.sp_addextendedproperty
  @name = N'MS_Description',
  @value = N'This is the bar column',
  @level0type = N'SCHEMA',
  @level0name = N'dbo',
  @level1type = N'TABLE',
  @level1name = N'foo',
  @level2type = N'COLUMN',
  @level2name = N'bar';

As you can see in the example, this syntax requires you to specify not only the extended property name/value pair and the individual components of the column's fully-qualified name, but also the types of each object. Updating and deleting extended properties using the system stored procedures requires the same type of information. A painful process to say the least.

The attached code sample creates an extended properties management schema named ExtProps. The ExtProps schema contains stored procedures that make it easier to add, update and delete extended properties. With these procedures you specify the object name using the standard SQL Server 1-, 2-, or 3-part naming convention along with the extended property name/value pair. The procedure will automatically figure out what type of object you're referencing. The following sample performs the same function as the previous example:

EXECUTE ExtProps.PropInsert N'dbo.foo.bar',
  N'MS_Description',
  N'This is the bar column';

The ExtProps.PropInsert, ExtProps.PropDelete and ExtProps.PropUpdate procedures use the object name you pass in to narrow down the type of object you're referencing. They then use the SQL Server catalog views to figure out exactly which object it should modify the extended properties on. The following table lists the types of object and the names expected by the procedures:

Name

Object Type

NULL

Current database

1-part

Schema

2-part (schema.object)

Table, View, Procedure, Function, Aggregate, Synonym, Queue

3-part (schema.object.column)

Column

Note that these procedures do not yet handle all the different objects to which you can assign extended properties. For instance, it does not currently handle extended properties for procedure parameters, event notifications, and a handful of others.

When you want to retrieve extended properties from the database you can use the standard system view, sys.extended_properties, or the fn_listextendedproperty system function. As an alternative, the ExtProps schema includes a new view -- ExtProps.Properties. This view has two advantages over the built-in system views:

  1. In addition to all the data returned by the sys.extended_properties view the ExtProps.Properties view also includes schema, object, and column names.
  2. The ExtProps.Properties view has triggers built on it that allow you to insert, delete, and update extended properties using standard SQL DML statements. The triggers actually call the relevant stored procedures, once for each row of extended property data being modified.

The figure below show sample output from the ExtProps.Properties view:

ExtProps.Properties View

The script attached to this posting creates the ExtProps schema, procedures and view.

Published Tuesday, January 12, 2010 1:18 AM by Mike C

Attachment(s): ExtProps.zip

Comments

 

mjswart said:

Very cool. I've had to do something similar in the past (including encryption/decryption!)

January 12, 2010 9:41 AM
 

Adam Machanic said:

According to T-SQL Tuesday rules as ratified by me in the first and second T-SQL Tuesday posts, the T-SQL

February 8, 2010 2:15 PM
New Comments to this post are disabled

This Blog

Syndication

News

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement