Using SQL Server 2000 Extended Properties

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.

Page 2 of 2