Posted in 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:


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

]  ]  ]    ]


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


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


Similarly we can drop extended property of database and columns.

Hope you like this post.


I am Kapil Singh Kumawat working on SQL Server since last 5 years. I am from Jaipur, Rajasthan, India and currently working in Cognizant Technology Solutions as SQL Server Developer. I have a good experience in writing queries, performance tuning, SSIS, SSAS, Power BI, Data migration and database designing. Apart from database I have interest in travelling,watching football and listening music. My blogs are also published on

Leave a Reply

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

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

Google photo

You are commenting using your Google 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 )

Connecting to %s