Home > SQL Server > Add or Drop or Update Extended Property using SQL Function – SQL Server

Add or Drop or Update Extended Property using SQL Function – SQL Server


We have already learned about Extended property and fn_listextendedproperty function in the previous blog post. In this blog I will talk about three functions that are used to add, remove and update extended property.

sp_addextendedproperty : It adds a new extended property to a database object.

sp_updateextendedproperty : This procedure updates the value of an existing extended property.

sp_dropextendedproperty : This function drops an existing extended property.

Function Syntax

sp_addextendedproperty OR 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’ }
]
]
]
]

sp_dropextendedproperty
[ @name = ] { ‘property_name’ }
[ , [ @level0type = ] { ‘level0_object_type’ }
, [ @level0name = ] { ‘level0_object_name’ }
[ , [ @level1type = ] { ‘level1_object_type’ }
, [ @level1name = ] { ‘level1_object_name’ }
[ , [ @level2type = ] { ‘level2_object_type’ }
, [ @level2name = ] { ‘level2_object_name’ }
]
]
]
]

Argument details

Property Name : This is the name of the user defined property name.

value : this is a sql_variant data type and the character cannot be more than 7,500 bytes.

Level 0 (object type, object name)

Valid Object type : ASSEMBLY, CONTRACT, EVENT NOTIFICATION, FILEGROUP, MESSAGE TYPE, PARTITION FUNCTION, PARTITION SCHEME, REMOTE SERVICE BINDING, ROUTE, SCHEMA, SERVICE, TRIGGER, TYPE, USER, and NULL. Note: This argument will be removed from the future version of SQL Server. So, please avoid using in the new development.

Object name: User defined actual object name or NULL or default

Level 1 (object type, object name)

Valid object type: AGGREGATE, DEFAULT, FUNCTION, LOGICAL FILE NAME, PROCEDURE, QUEUE, RULE, SYNONYM, TABLE, TYPE, VIEW, XML SCHEMA COLLECTION, and NULL.

object name: User defined actual object name or NULL or default

Level 2 (object type, object name)

Valid object type:  COLUMN, CONSTRAINT, EVENT NOTIFICATION, INDEX, PARAMETER, TRIGGER, and NULL.

object name: User defined actual object name or NULL or default

Experiment

In this demo, I am going to add, update and delete an extended property in the database object.

1. Adding extended property

exec sp_addextendedproperty
@name = ‘SQL Server Rider’,
@value = ‘This is a test’
GO

pic1

2. Updating extended property

exec sp_updateextendedproperty
@name = ‘SQL Server Rider’,
@value = ‘Property Updated’
GO

pic2

3. Delete extended property

exec sp_dropextendedproperty
@name = ‘SQL Server Rider’

GO

pic3

Advertisements
  1. No comments yet.
  1. No trackbacks yet.

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

%d bloggers like this: