MSSQL Sp_addextendedproperty usage, function

Sp_addextendedproperty [@name =] {' property_name '} [, [@value =] {' Value '} [, [@level0type =] {' L Evel0_object_type '} , [@level0name =] {' Level0_object_name '} [, [@level1type =] {' Level1_object_type '} , [@level1name =] {' Level1_object_name '} [, [@level2type =] {' Level2_object_type '} , [@level2name =] {' Level2_object_name '}]]

Notes

To specify extended properties, the objects in the SQL Server database are divided into three levels (0, 1, and 2). Level 0 is the highest level and is defined as an object that is contained in the database scope. Level 1 objects are included in the schema scope or user scope, and level 2 objects are contained in Level 1 objects. You can define extended properties for objects at any level in these levels.

References to objects in a level must be restricted with the name of a higher-level object that owns or contains them. For example, when you add an extended property to a table column (Level 2), you must also specify the table name (Level 1) that contains the column, and the schema that contains the table (level 0).

For a complete list of the objects and their valid levels 0, 1, and 2 types, see Using Extended Properties on database objects.

If all object types and names are empty, the property belongs to the current database itself.

Extended properties are not allowed for system objects, objects other than the scope of the user-defined database, or objects that are not listed as valid inputs in Arguments.

Schemas and users

In earlier versions of SQL Server, users owned database objects such as tables, views, and triggers. Therefore, extended attributes are allowed to be added to one of these objects and the user name is specified as a level 0 type. However, in SQL Server 2005, database objects are included in the schema. They are independent of the user who owns the schema.

In SQL Server 2005, we recommend that you do not specify USER as the level 0 type when applying extended properties to database objects, because this results in ambiguous name resolution. For example, assume that a user Mary has two schemas (Mary and MySchema), and both schemas contain a table named MyTable. If Mary adds an extended property to the table MyTable and specifies @level0type = N ' USER ', @level0name = Mary, then which table is not explicitly applied to the extended attribute. To maintain backward compatibility, SQL Server applies attributes to the tables contained by the schema named Mary. For more information about users and schemas, see User schema separation.

Parameters

[@name] = {' Property_name '}

The name of the property to add. The property_name data type is sysname and cannot be NULL. The name can also include a space or non-alphanumeric string, and a binary value.

[@value =] {' Value '}

The value to associate with the property. The data type of value is sql_variant, and the default value is NULL. Value cannot exceed 7,500 bytes in size.

[@level0type =] {' Level0_object_type '}

The type of the level 0 object. The Level0_object_type data type is varchar (128) and the default value is NULL.

Valid inputs include: ASSEMBLY, contract, EVENT NOTIFICATION, FILEGROUP, MESSAGE TYPE, PARTITION FUNCTION, PARTITION SCHEME, REMOTE Service BINDING, ROUTE, SCHEMA, service, USER, TRIGGER, TYPE, and NULL.

Important matters:
USER and type as Level 0 types will be removed in a future version of SQL Server. Avoid using these features in new development work, and consider modifying applications that currently use these features. Use SCHEMA instead of USER as Level 0 type. For type, use the SCHEMA as the level 0 type, using type as the Level 1 type.

[@level0name =] {' Level0_object_name '}

The name of the level 0 object type specified. The Level0_object_name data type is sysname and the default value is NULL.

[@level1type =] {' Level1_object_type '}

The type of the Level 1 object. The Level1_object_type data type is varchar (128) and the default value is NULL. Valid inputs include: AGGREGATE, DEFAULT, FUNCTION, LOGICAL FILE NAME, PROCEDURE, QUEUE, RULE, synonym, TABLE, TYPE, VIEW, XML SCHEMA COLLECTION and NULL.

[@level1name =] {' Level1_object_name '}

The name of the Level 1 object type specified. The Level1_object_name data type is sysname and the default value is NULL.

[@level2type =] {' Level2_object_type '}

The type of the Level 2 object. The Level2_object_type data type is varchar (128) and the default value is NULL. Valid inputs include: COLUMN, CONSTRAINT, EVENT NOTIFICATION, INDEX, PARAMETER, TRIGGER, and NULL.

[@level2name =] {' Level2_object_name '}

The name of the level 2 object type specified. The Level2_object_name data type is sysname and the default value is NULL.

Return code value

0 (Success) or 1 (failed)

EXEC sys.sp_addextendedproperty @name =n ' ms_description ', @value =n ' 0: Cancel, 1: reached, 2: not yet, 3: Book default ', @level0type =n ' SCHEMA ', @ Level0name=n ' dbo ', @level1type =n ' TABLE ', @level1name =n ' Tab_passenger ', @level2type =n ' COLUMN ', @level2name =n ' Passengerstate '

-Table and field Description Information Processing example-creating table Create tables table (A1 varchar, a2 char (2))--Add description information to the table execute sp_addextendedproperty N ' ms_description ', ' Personnel information table ', n ' user ', n ' dbo ', n ' table ', n ' table ', NULL, null--Add description information for field A1 execute Sp_addextendedproperty N ' ms_description ', ' Name ', n ' user ', n ' dbo ', n ' table ', n ' tables ', n ' column ', n ' A1 '--Add description information for field A2 execute sp_addextendedproperty N ' ms_description ', ' Gender ', n ' user ', n ' dbo ', n ' table ', n ' tables ', n ' column ', n ' A2 '--Update the Description property of column A1 in table: EXEC sp_updateextendedproperty ' ms_description ', ' Field 1 ', ' user ', dbo, ' table ', ' table ', ' column ', a1--delete the description attribute of column A1 in the table: EXEC sp_dropextendedproperty ' ms_description ', ' user ', dbo, ' Table ', ' table ', ' column ', a1--delete test drop table

(turn) MSSQL sp_addextendedproperty usage, function