sp_updateextendedproperty – Day 3 – SQL Server System Stored Procedure

Hi friends, today we will continue with other extended properties related stored procedure which is sp_updateextendedproperty.

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'

Now when we checked in extended properties of table books we will find the updated value.

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.

Regards,

Kapil Singh

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

Follow me on Twitter