Stored Procedure

Drop extended property using sp_dropextendedproperty in SQL Server

Hi friends today we will continue with other extended properties related stored procedure sp_dropextendedproperty.

Sp_dropextendedproperty is used to drop an existing extended property.

Syntax of sp_dropextendedproperty is as follows:

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

]  ]  ]    ]

Arguments

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

@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.

@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 drop the extended property of table books which we added in our. article of sp_addextendedproperty here.

–Drop extended property of a table

EXEC sp_dropextendedproperty

@name = ‘caption’,

@level0type = ‘Schema’,

@level0name = ‘dbo’,

@level1type = ‘Table’,

@level1name = ‘books’

sp_dropextendprop1

Now when we checked in extended properties of table books we didn’t find any extended properties as we have already dropped that.

sp_dropextendprop2

Similarly we can drop extended property of database and columns.

Hope you like this post.

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