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'

sp_updateextendprop1

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

sp_updateextendprop2

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

 

Avatar

About Kapil Singh Kumawat

Kapil Singh Kumawat has been working with SQL Server since last 5 years. He is from Jaipur, Rajasthan, India and currently working with Cognizant Technology Solutions as SQL Server Developer. He has good experience in performance tuning, SSIS, data migration and data designing. Apart from database he has interest in travelling, watching football and listening music.

View all posts by Kapil Singh Kumawat →

Leave a Reply

Your email address will not be published. Required fields are marked *