I have not used SQL statements to add notes to enterprise manager before: (I checked the information and learned that Microsoft introduced extended attributes in SQL Server 2000, you can define these attributes on various database objects. These extended attributes can be used to store application or site-specific information related to database objects. You can use sp_addextendedproperty to add the new extended property to the database object. If the property already exists, the process fails. Usage: sp_addextendedproperty
[@ Name =] {'Property _ name '}
[, [@ Value =] {'value '}
[, [@ Level0type =] {'level0 _ object_type '}
, [@ Level0name =] {'vel0 _ object_name '}
[, [@ Level1type =] {'vel1 _ object_type '}
, [@ Level1name =] {'vel1 _ object_name '}
[, [@ Level2type =] {'vel2 _ object_type '}
, [@ Level2name =] {'vel2 _ object_name '}
]
]
]
]
Parameters
[@ Name =] {'Property _ name '}Name of the property to be added. The data type of property_name is sysname, which cannot be null. The name may also contain blank or non-alphanumeric strings and binary values.
Note: When property_name = 'Ms _ description', add a comment
[@ Value =] {'value '}The value to be associated with the attribute. The data type of value is SQL _variant, with the default value null. The value size cannot exceed 7,500 bytes; otherwise, SQL Server will produce an error.
[@ Level0type =] {'level0 _ object_type '}User or user-defined type. The data type of level0_object_type is varchar (128), and its default value is null. Valid inputs include user, type, and null.
[@ Level0name =] {'level0 _ object_name '}The name of the specified level 0 object type. The data type of level0_object_name is sysname, and its default value is null.
[@ Level1type =] {'level1 _ object_type '}Type of level 1 objects. The data type of level1_object_type is varchar (128), and its default value is null. Valid inputs include table, view, procedure, function, default, rule, and null.
[@ Level1name =] {'level1 _ object_name '}The name of the specified level 1 object type. The data type of level1_object_name is sysname, and its default value is null.
[@ Level2type =] {'vel2 _ object_type '}Type of level 2 objects. The data type of level2_object_type is varchar (128), and its default value is null. Valid inputs include column, parameter, index, constraint, trigger, and null.
[@ Level2name =] {'level2 _ object_name '}The name of the specified level 2 object type. The data type of level2_object_name is sysname, and its default value is null.
Return code value
0 (successful) or 1 (failed)
Note1. System Objects cannot have extended attributes. 2. objects are classified by level. The value 0 indicates the highest level and the value 2 indicates the lowest level. When you add, update, or delete extended attributes, you must specify all more advanced objects. For example, to add an extended attribute to a level 1 object, you must specify all level 0 information. If you want to add extended attributes to Level 2 objects, you must provide all information about level 0 and Level 1. 3. At each level, the object type and object name can uniquely identify the object. If either party in a pair is specified, the other party must be specified. 4. Valid property_name and value are given. If no object type or name exists, the attribute belongs to the current database. If you specify the object type and name, you must also specify the parent object and type. Otherwise, SQL Server produces an error.
Permission
Members of the db_owner and db_ddladmin fixed database roles can add extended attributes to any object. Users can add extended attributes for their own objects. However, only db_owner can add attributes to the user name.
Example
The following example adds a comment to the "ID" column of table "T1:
Create Table T1 (ID int, name char (20) goexec sp_addextendedproperty 'Ms _ description', 'employee id', 'user', DBO, 'table', T1, 'column ', ID
In additionSp_updateextendedproperty: update the value of an existing extended property. Sp_dropextendedproperty: removes existing extended attributes. Fn_listextendedproperty: used to retrieve the value of an existing extended property. In Oracle, comment statements can be used to add comments to the column, as shown below: Comment on column employees. job_id
Is 'abbreviated job title'; Delete comment: Comment on column employees. job_id is ''; for more detailed syntax, see Oracle documentation
