Database Using SQL Server 2000 Extended Properties

Using SQL Server 2000 Extended Properties

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.

Latest Posts

Related Stories