Extended Properties in SQL Server 2000

Mike Gunderloy

SQL Server 2000 lets developers extend the properties of many common database objects. This scheme of extended properties allows you to define such things as a column's caption or a table's description in a standard manner. In this article, Mike Gunderloy takes a look at the syntax and use of SQL Server extended properties.

Who says that Microsoft never shares innovations between its different databases? For many years, users of Microsoft's Jet engine have had the benefit of user-defined properties: properties of database objects that the developer can name and populate in a free-form fashion. Now, with SQL Server 2000, the SQL Server engine finally has the equivalent capability thanks to the introduction of extended properties. Extended properties provide a standardized way to add extra information to the core database objects. SQL Server itself makes use of extended properties in the Enterprise Manager interface. For example, when you design a table through Enterprise Manager, you can add a "description" property to any column in the table. This property is stored as an extended property named MS_Description.

Other client programs can also make use of extended properties to add to the schema information that SQL Server stored. Microsoft Access 2002 is a case in point. Access 2002 makes extensive use of these properties to store information about the way that SQL Server tables are used in Access Data Projects (ADPs). If you're interested in finding out about extended properties from an Access 2002 point of view, you may want to read my article in the June 2001 issue of Pinnacle's Smart Access. In this article, I'll take a look at the subject from the SQL Server point of view.

The mechanics of extended properties

Hard-core developers and DBAs don't just want to know that SQL Server itself uses extended properties. We want to be able to create them ourselves. Luckily for us, there are only three stored procedures and one function that we need to master:

• sp_addextendedproperty, which adds a new extended property to a database object;
• sp_dropextendedproperty, which removes an extended property from a database object;
• sp_updateextendedproperty, which updates the value of an existing extended property; and
• the fn_listextendedproperty function, which retrieves the value of an extended property or the list of all extended properties from a database object.

Note that you're not allowed to set extended properties on system objects or temporary objects. This technology is strictly for customizing your own permanent database objects.

Let's start with sp_addextendedproperty. Here's an example that uses this stored procedure to add an extended property named Caption to a column named id in a table named MyTable:

sp_addextendedproperty 'Caption', 'Enter ID:', 
'user', 'dbo', 'table', 'MyTable', 'column', 'id'

The first argument to this stored procedure is the name of the extended property to create, and the second is the initial value to assign to the extended property. The next six arguments, which you'll see repeat in the other extended property calls, are as follows:

• Level 0 type
• Level 0 name
• Level 1 type
• Level 1 name
• Level 2 type
• Level 2 name

To understand these, take a look at Table 1, which shows the various available types for extended properties.

To specify an object, you must specify the types and corresponding names, with NULLs for trailing parameters. For example, the table dbo.authors is specified as:

'user', 'dbo', 'table', 'authors', NULL, NULL

while the column LastName in this table would be specified as:

'user', 'dbo', 'table', 'authors', 'column', 
'LastName'

You can also create extended properties for the current database itself by specifying NULL for all six type and name parameters.

Extended property values use the new sql_variant datatype, which can hold pretty much anything you throw at it. Recall that, in general, the sql_variant datatype is limited to 8016 bytes. However, values for extended properties are limited to 7500 bytes.

When you want to see the existing extended properties for an object, you can use the fn_listextendedproperty function. For example, to see all of the extended properties on the authors table, you could execute this query:

SELECT name, value FROM ::fn_listextendedproperty 
(NULL, 'user', 'dbo', 'table', 'authors', NULL, NULL)

The first argument to the function (which needs to be prefixed by double colons because it's a system-supplied function; this syntax is new in SQL Server 2000) is either the name of an extended property or NULL to retrieve all extended properties.

To delete an extended property, you pass the property name and object identifier to sp_dropextendedproperty. For example, here's the T-SQL to delete the Caption property from the id column in the table named MyTable:

sp_dropextendedproperty 'Caption', 'user', 'dbo', 
'table', 'MyTable', 'column', 'id'

Finally, sp_updateextendedproperty is very similar to sp_addextendedproperty, except that sp_updateextendedproperty will raise an error if the property doesn't already exist:

sp_updateextendedproperty 'Caption', 'A new caption', 
'user', 'dbo', 'table', 'MyTable', 'column', 'id'

If you prefer to manipulate extended properties via a graphical user interface, you can use the Object Browser that's built into SQL Query Analyzer. To use this interface, expand the Object Browser tree until you locate the object of interest. Right-click on the object and select Extended Properties. This will open the Extended Property dialog box, shown in Figure 1.

In the Extended Property dialog box, the buttons to the right of the list allow you to add or drop extended properties. To change the value of an existing extended property, click in the current value and type your changes. Then click the OK or Apply button to save the change.

Under the hood

As you might guess, extended properties are stored in a system table. To be precise, they're stored in sysproperties, which is present in every database (whether any extended properties have ever been defined in that database or not). Table 2 shows the structure of sysproperties.

Of course, to ensure compatibility with future versions of SQL Server, you should never work directly with the sysproperties table. Use the stored procedures and function instead.

Teething pains

Not surprisingly, given that this is the first release of the technology, there are a few problems with extended properties in the initial release of SQL Server 2000. First, there's a potential buffer overflow in the SQL-DMO code used for creating object scripts. If you have an object with a lot of extended properties—or very long extended property values—you may encounter an access violation error when trying to script the object. To work around the problem, you can uncheck the box for Include Extended Properties when generating scripts from SQL Enterprise Manager. This problem is documented in Knowledge Base article Q279183 and is fixed in SQL Server 2000 Service Pack 1.

Second, you can get into trouble if you use SQL Enterprise Manager to create a table. In particular, if you create a column, assign a description to that column (which tells SQL Server to create an extended property), delete the column, and then try to save the table, you'll encounter an error that's similar to the following:

'Table1' table
- Unable to create table.  
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL 
Server]Object is invalid. Extended properties are not 
permitted on 'dbo.Table1.DeletedColumn', or the object 
does not exist.

To work around this error, dismiss the error message, and then use the Save Change Script toolbar button in the table designer to save a SQL script for the new table. Open the SQL script with a text editor and remove the erroneous call to sp_addextendedproperty. You can then execute the SQL script using SQL Query Analyzer to create the table. This problem is documented in Knowledge Base article Q278016 and is not fixed in either SP1 or SP2.

Extended properties: Are they worth using?

If you work with extended properties for a bit, and use sp_helptext to look at the stored procedures that manipulate them, you'll realize that there's nothing deep and mysterious about their implementation. Any developer who needs extra information for SQL Server objects could design a new table that's related to sysobjects to hold that information. So why use extended properties for this purpose? The answer is twofold. First, the few remaining bugs notwithstanding, extended properties give you a tested and standardized place to store that information. If you ever need to share additional information with another developer, extended properties give you a way to do so without requiring negotiation of data structures and protocols. Second, extended properties deliver this storage in a version-independent fashion. Even in the (probably unlikely) event of changes to sysobjects, the stored procedures for working with extended properties should continue working. Extended properties provide an ideal place for your additional database object information without requiring you to write any code, and that's good enough to make them very useful indeed.

To find out more about SQL Server Professional and Pinnacle Publishing, visit their website at http://www.pinpub.com/html/main.isx?sub=57

Note: This is not a Microsoft Corporation website. Microsoft is not responsible for its content.

This article is reproduced from the January 2002 issue of SQL Server Professional. Copyright 2002, by Pinnacle Publishing, Inc., unless otherwise noted. All rights are reserved. SQL Server Professional is an independently produced publication of Pinnacle Publishing, Inc. No part of this article may be used or reproduced in any fashion (except in brief quotations used in critical articles and reviews) without prior consent of Pinnacle Publishing, Inc. To contact Pinnacle Publishing, Inc., please call 1-800-493-4867 x4209.