Creating a SQL Server 2000 Extended Property

When you use Enterprise Manager in SQL Server 2000 to create a table, the bottom half of the screen lists several properties of the selected column: Description, Default Value, Precision, Scale, Identity, Identity Seed, Identity Increment, Is RowGuid, Formula, and Collation.

How can I use a SELECT statement or function to return the Description property for a particular column?

Enterprise Manager creates and stores the Description property as an extended property. You can use extended properties to store application- or site-specific information about the database and the following database objects: tables, views, procedures, functions, defaults, rules, columns, parameters, indexes, constraints, and triggers.

You use three system stored procedures and a function to create and manage extended properties:

Enterprise Manager uses these commands for creating, managing, and retrieving the description property. Web Figure 1 shows how to add a description for the au_id column of the authors table in the Pubs database. The following query shows how you can use fn_listextendedproperty() to retrieve the extended property information you just added:

SELECT   *FROM   ::fn_listextendedproperty (NULL, 'user', 'dbo', 'table', 'authors', 'column', default)

This code produces a table that describes the extended property, named MS_Description. This naming taxonomy is consistent with objects that you name through Enterprise Manager. For more information about extended properties, see Bill Vaughn, "Managing Extended Properties," July 2001, and the "Property Management" topic in SQL Server Books Online (BOL).