Extended Properties in SQL Server

Introduction

Extended Properties in SQL Server allows us to create additional customized properties to store additional information. Extended Properties are a way to create a self-documenting database. Every Extended Property has a user defined name and value. The value of Extended Properties is a SQL variant and can contain up to 7500 bytes of data. A single object may have multiple Extended Properties. One main advantage of an Extended Property is that they are backed up with the database, and scripted out with the build scripts. Extended Properties should not be used to hide an object's sensitive information, in other words any user with the right to view the object, can view the values of Extended Properties. Using Extended Properties, we can store text such as descriptive or instructional content as properties of the database object.

Create Extended Properties of Database object

Using the Stored Procedure "sp_addextendedproperty" we can add a new Extended Property of the database object. The Stored Procedure sp_addextendedproperty has many mandatory (@name) and optional parameters (@value, @level0type, @level0name, @level1type, @level1name, @level2type and @level2name). Stored Procedure "sp_addextendedproperty" returns 0 if the Extended Property is added successfully else it returns 1.

USE [TESTDB]
EXEC sys.sp_addextendedproperty
@name = N'DatabaseVersion',
@value = N'10.2.3.0'

Update Extended Properties of Database object

Using Stored Procedure "sp_updateextendedproperty" we can update the existing Extended Property of a database object. Stored Procedure sp_updateextendedproperty has many mandatory (@name) and optional parameter (@value, @level0type, @level0name, @level1type, @level1name, @level2type and @level2name). Stored Procedure "sp_updateextendedproperty" returns 0 if the Extended Property is added successfully else returns 1.

USE [TESTDB]
EXEC sys.sp_updateextendedproperty
@name = N'DatabaseVersion',
@value = N'10.2.3.1'

Delete / Drop Extended Properties of Database object

Using Stored Procedure "sp_dropextendedproperty" we can delete / drop the existing Extended Property of a database object. Stored Procedure sp_dropextendedproperty has many mandatory (@name) and optional parameters (@value, @level0type, @level0name, @level1type, @level1name, @level2type and @level2name). Stored Procedure "sp_dropextendedproperty" returns 0 if the Extended Property is added successfully else returns 1.

USE [TESTDB]
EXEC sp_dropextendedproperty
@name = N'DatabaseVersion'

View an existing Extended Property

We have three ways to view an existing Extended Property.

1. Using table "sys.extended_properties"
   
   The "sys.extended_properties" table contains all the Extended Properties of the database. We can filter it by the name or "class_desc" column.
   
   SELECT* FROMsys.extended_properties
   
   
    
2. Using the "fn_listextendedproperty" function
   
   "fn_listextendedproperty" is a builtin function, it returns Extended Property values of the database object.
   
   SELECT name, value FROMfn_listextendedproperty(default,default,default,default,default,default,default)
   
   
   
   The "fn_listextendedproperty" function takes a number of parameters, please refer to fn_listextendedproperty for more details.
    
3. Graphical View
   
   We can also determine the value of the Extended Properties of a database object in graphical mode. Right-click on any database object and click on properties.
   
   
   

This will open the property window of the database object. Now select the "Extended Properties" tab, that show all Extended Properties of the database object. We can also modify the value of the Extended Properties from this window.



Note: Extended Properties are replicated only when the initial synchronization occurrs between the Publisher and the Subscriber. Extended Properties cannot replicate if we add or modify an Extended Property after the initial synchronization. We cannot create Extended Properties on system objects or outside of the user defined database. The "Information_Schema" view does not provide the list of Extended Properties.

Conclusion

Extended Properties in SQL Server allows us to create additional customized properties that can be used in a trigger, function, table, view, parameter, Stored Procedure, index, constraint or column.