Stored Procedure

How to add extended property using Sp_addextendedproperty Stored Procedure in SQL Server

Hi friends, in this blog you will learn about sp_addextendedproperty in sql server.

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

sp_addextendprop1

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

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

sp_addextendprop2

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.

Advertisements

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s