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