http://www.developer.com/

Back to article

Using SQL Server 2000 Extended Properties


June 2, 2004

It's safe to say that more developers are aware of the importance of metadata today than ever before. Metadata - data about data - opens up vistas in code generation and intelligent tools that most of us didn't even dream of a few years ago. To take a very simple example, Figure 1 shows a form automatically generated by Microsoft Access. Access "knows" to use a checkbox for the Discontinued field and dropdown lists for Suppliers and Categories by inspecting the data types and relationships within the database. That's not information contained in the actual data, but information about the data: metadata.

Autogenerated Access form

But have you ever thought of going one step further and customizing the metadata associated with your data? You can do just that with SQL Server data by using extended properties. Read on to find out how.

Understanding Extended Properties

A column in SQL Server has a set of built-in properties such as its data type, precision, scale, whether it is an identity column, and so on. With extended properties, you can add as many additional properties to the built-in list as you choose. SQL Server 2000 provides three system stored procedures and a function for dealing with extended properties:

  • sp_addextendedproperty adds a new extended property to a database object
  • sp_dropextendedproperty removes an extended property from a database object
  • sp_updateextendedproperty updates the value of an existing extended property
  • fn_listextendedproperty retrieves the value of an extended property or the list of all extended properties from a database object

Though I'll show you how to use these tools with a database column, they apply to many other database objects. In fact, you can manipulate extended properties on tables, views, stored procedures, parameters, functions, and other objects. Refer to SQL Server Books Online for the complete list.

Creating New Extended Properties

For starters, I'm going to add extended properties named BackColor and ForeColor to the CompanyName column in the Customers table in the Northwind sample database. You can see how these might be useful to design tools; it's easy to imagine an enhanced version of the Microsoft Access forms wizard that would use these extended properties, if they were present, to determine the colors to use for a control displaying data from this field. As you'll see in a bit, the value of an extended property is available to any tool that can execute T-SQL statements.

To add these two extended properties, I use the sp_addextendedproperty stored procedure twice, once for each property. Here are hte calls together with the responses you'll get in Query Analyzer:


sp_addextendedproperty 'BackColor', 'Blue', 
'user', 'dbo', 'table', 'Customers', 'column', 'CompanyName'
GO
sp_addextendedproperty 'ForeColor', 'Yellow', 
'user', 'dbo', 'table', 'Customers', 'column', 'CompanyName'
GO

(1 row(s) affected)

(1 row(s) affected)

As you can see, the call to create the extended property includes eight parameters. The first two of these specify the name of the property and the initial value for the property. The other six give SQL Server instructions for locating the appropriate database object: in this case, the CompanyName column in the Customers table owned by the dbo user.

Setting Extended Property Values

As with other properties, you can change the values of extended properties. To do this, you use the sp_updateextendedproperty stored procedure. For example:


sp_updateextendedproperty 'BackColor', 'Red', 
'user', 'dbo', 'table', 'Customers', 'column', 'CompanyName'
GO
sp_updateextendedproperty 'ForeColor', 'Green', 
'user', 'dbo', 'table', 'Customers', 'column', 'CompanyName'
GO

(1 row(s) affected)

(1 row(s) affected) 

If you attempt to update the value of an extended property that hasn't yet been created, SQL Server will raise a level 16 error number 15217, "Property cannot be updated or deleted."

Retrieving Extended Properties

Getting back information from extended properties is the job of the fn_listextendedproperty system-supplied function. There are two variants to this function. If you don't know the name of the extended property, you can pass NULL as the first argument to get back a list of all the extended properties on the specified database object:


SELECT name, value FROM ::fn_listextendedproperty 
(NULL, 'user', 'dbo', 'table', 'Customers', 'column', 'CompanyName')

name        value
----------- ------ 
BackColor   Red
ForeColor   Green

(2 row(s) affected)

If you know the name of the property whose value you'd like to retrieve, you can specify it in the call to fn_listextendedproperty and get back the value of only that property:


SELECT name, value FROM ::fn_listextendedproperty 
('ForeColor', 'user', 'dbo', 'table', 'Customers', 'column', 'CompanyName')

name        value 
----------- ------  
ForeColor   Green

(1 row(s) affected) 

Deleting Extended Properties

Finally, there may come a time when you'd like to clean up the extended properties on a database object. You can do this with the sp_dropextendedproperty stored procedure:


sp_dropextendedproperty 'ForeColor', 'user', 'dbo', 'table', 
 'Customers', 'column', 'CompanyName'
GO
sp_dropextendedproperty 'BackColor', 'user', 'dbo', 'table', 
 'Customers', 'column', 'CompanyName'
GO

(1 row(s) affected)

(1 row(s) affected) 

Metadata for Today and Tomorrow

It's always wise to exercise a bit of caution when using a feature that's unique to one particular implementation of a product. Extended properties are a Microsoft innovation that were launched with SQL Server 2000. Fortunately, you can feel safe about this particular innovation remaining supported for a long while to come. First, the early betas of SQL Server 2005 include enhanced support for extended properties that is backwards compatible with what SQL Server 2000 has today. Second, some of Microsoft's own products make use of SQL Server extended properties. That's pretty good insurance that you can do the same without fear of your code ending up obsolete. So, the next time you need metadata in SQL Server, think extended properties. You'll be glad you did.

Mike Gunderloy is the author of over 20 books and numerous articles on development topics, and the lead developer for Larkware. Check out his latest book, Coder to Developer from Sybex. When he's not writing code, Mike putters in the garden on his farm in eastern Washington state.

Sitemap | Contact Us

Thanks for your registration, follow us on our social networks to keep up-to-date