Hi friends, today we will continue with other extended properties related stored procedure which is sp_updateextendedproperty.

Sp_updateextendedproperty stored procedure is used to update the value of existing extended property.

Syntax of sp_updateextendedproperty is as follows:

 

Arguments

@name is name of the property and it cannot be NULL.

@value is the value associated with the property. The size of the value cannot be exceed than 7500 bytes.

@level0type is type of level 0 objects having datatype varchar(128) with a default of NULL. Valid inputs for level0type are ASSEMBLY, CONTRACT, EVENT NOTIFICATION, FILEGROUP, MESSAGE TYPE, PARTITION FUNCTION, PARTITION SCHEME, REMOTE SERVICE BINDING, ROUTE, SCHEMA, SERVICE, USER, TRIGGER, TYPE, PLAN GUIDE.

@level0name is the name of level0type object.

@level1type is type of level 1 objects having datatype varchar(128) with a default of NULL.  Valid inputs for level1type are AGGREGATE, DEFAULT, FUNCTION, LOGICAL FILE NAME, PROCEDURE, QUEUE, RULE, SYNONYM, TABLE, TABLE_TYPE, TYPE, VIEW, XML SCHEMA COLLECTION.

@level1name is the name of level1type object.

@level2type is the type of level 2 objects having datatype varchar(128) with a default of NULL. Valid inputs for level2type are COLUMN, CONSTRAINT, EVENT NOTIFICATION, INDEX, PARAMETER, TRIGGER.

@level2name is the name of level2type object.

Now we will modify the extended property of table books which we added in our previous article of sp_addextendedproperty here.

sp_updateextendprop1

Now when we checked in extended properties of table books we will find the updated value.

sp_updateextendprop2

Similarly we can update extended property to database and column as:

We will see another stored procedure of extended properties in our next blog in which we will see how to drop extended properties.

Regards,

Kapil Singh

Like us on FaceBook | Join the fastest growing SQL Server group on FaceBook 

Follow me on Twitter