Add/Modify/Remove annotation properties for columns in SQL Server tables (sp_addextendedproperty, Sp_updateextendedproperty, Sp_dropextendedproperty)

This basic full reference: Sql--sp_addextendedproperty and sp_updateextendedproperty (Transact-SQL)

Three stored procedure usages, take sp_addextendedproperty as an example:

sp_addextendedproperty[@name =]{'property_name' }     [, [@value =]{'value' }        [, [@level0type =]{'Level0_object_type' }         , [@level0name =]{'Level0_object_name' }              [, [@level1type =]{'Level1_object_type' }               , [@level1name =]{'Level1_object_name' }                     [, [@level2type =]{'Level2_object_type' }                      , [@level2name =]{'Level2_object_name' }                     ]              ]        ]    ]

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. level0_object_type is varchar ($), with a default of NULL." The data type of the >level0_object_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, PLAN Guide, and NULL.                                     

Important Notes
In future versions of SQL Server, the ability to specify USER as level 0 type in the extended properties of Level 1 type objects will be removed. use SCHEMA as the level 0 type. For example, when you define an extended property for a table, you specify the schema of the table instead of the user name. in future versions of SQL Server, the specified type will be removed as a Level 0 type feature.

[@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. level1_object_type is varchar ($), with a default of NULL." The data type of the >level1_object_type is varchar (128), and the default value is 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.

Example:

1. Create a Test table:

if exists(Select 1  fromSys.tableswhere object_id=object_id('testtable'))    begin        Drop TabletesttableEndCreate Tabletesttable (ColumnAvarchar( -), COLUMNBvarchar( -))

2. Add a comment to the Columna column:

execsp_addextendedproperty@name=N'ms_description',     @value=N'comments for column A',     @level0type=N'SCHEMA',    @level0name=N'dbo',     @level1type=N'TABLE',    @level1name=N'testtable',     @level2type=N'COLUMN',    @level2name=N'ColumnA'

3. View the results:

or execute the SQL statement:

Select  from sys.extended_properties A   Inner Join  on a.major_id=B.object_id and   a.minor_id=where a.major_id=object_id('testtable')

Results:

Add/Modify/Remove annotation properties for columns in SQL Server tables (sp_addextendedproperty, Sp_updateextendedproperty, Sp_dropextendedproperty)