sp_dropextendedproperty – Day 4 – SQL Server System Stored Procedure

Hi friends, today in SQL server system stored procedure series we will continue with another 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 modify 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.

Regards,

Kapil Singh

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

Follow me on Twitter