Stored Procedure

How to Update extended property using sp_updateextendedproperty in SQL Server

Hi friends, today in this blog we will discuss how to updated the extended property.

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

Syntax of sp_updateextendedproperty is as follows:

sp_updateextendedproperty

[ @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’ }

]

]

]

]

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.

–Modify extended property of a table

EXEC sp_updateextendedproperty

@name = ‘caption’,

@value = ‘This table contains information about books placed in a library’,

@level0type = ‘Schema’,

@level0name = ‘dbo’,

@level1type = ‘Table’,

@level1name = ‘books’

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:

–Updating extended property to database

EXEC sp_updateextendedproperty

@name = ‘caption’,

@value = ‘This is library database’

 

–Updating extended property to column

EXEC sp_updateextendedproperty

@name = ‘caption’,

@value = ‘Contains name of books’,

@level0type = ‘Schema’,

@level0name = ‘dbo’,

@level1type = ‘Table’,

@level1name = ‘books’,

@level2type = ‘Column’,

@level2name = ‘Name’

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

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s