Tip: Managing Data Dictionaries Based on SQL Server 2000 Extended Properties

You can browse extended properties in Query Analyzer and use the extended properties to manage metadata for schema-level objects in SQL Server environments. Sometimes you might need to document the business and technical definitions of columns in a table. A business definition implies a description suitable for an end user or business analyst; a technical definition might include additional descriptions for developers and DBAs.

Consistency is key to any metadata-management schema, including extended properties. Using definitions properly is difficult if everyone isn't using the same naming schema for extended properties. To ensure that developers create property names consistently, define and create extended properties before the developers need them, and leave the definitions empty. Your developers will have "blank" extended properties that they can complete with descriptions when they need them. Developers have fewer chances to mistype the names of extended properties that your metadata-management schema will use.

If you want to implement a consistent naming schema for extended properties in your development environment, you can start with the stored procedure that Listing A shows. SQL Server MVP Bob Pfeiff wrote the code as a template for a project. The procedure adds two extended properties called Comment and MS_Description to every column in a database. You can easily modify this code to suit any naming schema.

Comment is a generic name, but MS_Description has special meaning in the world of extended properties. You're probably familiar with the Description field in the Design Table dialog box in Enterprise Manager. SQL Server stores the value of this field in an extended property for the column that Enterprise Manager names MS_Description. You can create your own column-level extended property called MS_Description, and Enterprise Manager will display the value in the Description field of the table design pane. For more information about extended properties, see Bill Vaughn, "Managing Extended Properties," July 2001.