When you use SQL Server Enterprise Manager in Microsoft 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:
- sp_addextendedproperty
- sp_updateextendedproperty
- sp_dropextendedproperty
- fn_listextendedproperty()
Enterprise Manager uses these commands for creating, managing, and retrieving the description property. 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," http://www.sqlmag.com, InstantDoc ID 20886, and the "Property Management" topic in SQL Server Books Online (BOL).
Previous
Register
