February 27, 2021
Hot Topics:

Using SQL Server 2000 Extended Properties

  • By Mike Gunderloy
  • Send Email »
  • More Articles »

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'
sp_addextendedproperty 'ForeColor', 'Yellow', 
'user', 'dbo', 'table', 'Customers', 'column', 'CompanyName'

(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'
sp_updateextendedproperty 'ForeColor', 'Green', 
'user', 'dbo', 'table', 'Customers', 'column', 'CompanyName'

(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."

Page 1 of 2

This article was originally published on June 2, 2004

Enterprise Development Update

Don't miss an article. Subscribe to our newsletter below.

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