Extended Properties in SQL Server

Introduction

Extended Properties are a way to create a self-documenting database. Extended Properties in SQL Server allows us to create additional customized properties to store additional information. 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 it is backed up with the database and scripted 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.

Update Extended Properties of Database object

Using the 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 the 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.

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

Using the "fn_listextendedproperty" function

"fn_listextendedproperty" is a built-in 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 several parameters, and please refer to fn_listextendedproperty for more details.

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.

Now select the "Extended Properties" tab, which shows all Extended Properties of the database object. This will open the property window 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 occurs 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 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 customized properties that can be used in a trigger, function, table, view, parameter, Stored Procedure, index, constraint, or column.