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
This article was originally published on June 2, 2004