sp_addextendedproperty – Day 2 – SQL Server System Stored Procedure

Hi friends, today in SQL server system stored procedure series we will look into stored procedure sp_addextendedproperty.

Sp_addextendedproperty stored procedure is used to create metadata about our database objects. Using this stored procedure we can put information about Sql server objects like name of developer who created the object, to track the version of objects inside database.

Extended properties are not applicable on system objects, objects outside the scope of user defined function.

Syntax of sp_addextendedproperty is as follows:

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

Let’s take an example and add extended property to a table.

I have created a sample table book for this whose definition is as follows:

CREATE TABLE books
(
 ID INT,
 Name VARCHAR(15)
)

--Adding extended property to table
EXEC sp_addextendedproperty 
	@name = 'caption',
	@value = 'This table stores information about books',     
	@level0type = 'Schema',
	@level0name = 'dbo',
	@level1type = 'Table',
	@level1name = 'books'

After executing stored procedure we can check that extended property using SSMS.

Right Click to tablename -> Select Properties -> Select Extended Properties

Similarly we can add extended property to database and column as:

--Adding extended property to database

EXEC sp_addextendedproperty 
	@name = 'caption',
	@value = 'This database stores information about library'


--Adding extended property to column

EXEC sp_addextendedproperty 
	@name = 'caption',
	@value = 'This column stores information about name of books',
	@level0type = 'Schema',
	@level0name = 'dbo',
	@level1type = 'Table',
	@level1name = 'books',
	@level2type = 'Column',
	@level2name = 'Name'

We will continue with other stored procedures of extended properties in next part of this series.

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