Setting extended property to an object helps the developer/DBA to know the purpose of that object. It helps you to define/store metadata in the database closely tied to database objects.
Syntax:
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' }
]
]
]
]
[;]
Example of setting extended property to a schema ,
EXECUTE sp_addextendedproperty
@name = 'Schema_Description',
@value = 'Contains objects related to Schema DBO.',
@level0type = 'SCHEMA ',
@level0name = 'DBO '
This type of description helps developers/programmer/DBA a lot to understand the purpose of that object. Also, represents the clear metadata of that object...
Setting extended property for a column looks as below,
EXECUTE sys.sp_addextendedproperty
@name = N'LoginKey_Description',
@value = N'Login Password, encrypt the password through the system and save it here',
@level0type = N'SCHEMA',
@level0name = N'dbo',
@level1type = N'TABLE',
@level1name = N'Application_User',
@level2type = N'COLUMN',
@level2name = N'LoginKey';