Sqlserver using extended attributes on database objects

SQL Server 2000 introduces extended attributes that can be defined on various database objects. These extended attributes can be used to store application or site-specific information related to database objects. Because the attribute is stored in the database, all applications that read the attribute can evaluate the object in the same way. This helps enforce data consistency among all programs in the system.

Each extension property has a user-defined name and value. The extended property value can contain up to 7,500 bytes of dataSQL _variantValue.

Extended attributes may be used for the following purposes:

1. Specifies the title of a table, view, or column. In this way, all applications can use this title on the user interface that displays table, view, or column information.
2. Specifies the input mask of the column so that all applications can verify the data before executing the Transact-SQL statement.
3. Specifies the format rules for displaying data in columns.
4. Record the description of a specific database object that the application can display to the user.
5. Specify the column size and window position to be displayed.

SQL Server 2000 provides three system stored procedures and a function for operating extended attributes. They are:

• Sp_addextendedproperty(Add new extended attributes to the database object .)
• Sp_updateextendedproperty(Update the values of existing extended attributes .)
• Sp_dropextendedproperty(Remove existing extended attributes .)
• Fn_listextendedproperty(List extended attributes)

The following example illustrates the usage of these functions. I plan to add a Description extension attribute for Column CategoryID In the table Categories in the example database Northwind provided by SQLServer to describe the purpose of CategoryID. You can do the following:
 
Sp_addextendedproperty 'description', 'Directory number', 'user', dbo, 'table', Categories, 'column', CategoryID

The Parameters specify the extended attributes. The value of the extended attributes. The level 0 object is user, the level 0 Object Name Is dbo, the level 1 object type is table, and the Level 1 Object Name Is Gategories, the second-level object is column, and the second-level Object Name Is GategoryID.

Run the following code to view the added result:
Select * from: fn_listextendedproperty ('description', 'user', 'dbo', 'table', 'category', 'column', 'categoryid ')

The Parameters specify the extended attributes, level 0 objects, level 0 object names, level 1 objects, level 1 object names, level 2 objects, and level 2 object names.

The list is as follows:
Objtype objname name value
COLUMN CategoryID Description Directory number

The other two stored procedures use the same method.

Forgive me link: http://www.cnblogs.com/iaxes/articles/30799.html