Extended Property – SQL Server

Extended property in the SQL Server help us to store name and value pair data in the database objects.

Extended properties can be used for the following:

Specifying a caption for a table, view, or column. Applications can then use the same caption in a user interface that displays information from that table, view, or column.

Specifying an input mask for a column so that applications can validate data before running a Transact-SQL statement. For example, the required format for a postal code or telephone number column can be specified in the extended property.

Specifying formatting rules for displaying the data in a column.

Recording a description of specific database objects that applications can display to users. For example, the descriptions may be used in a data dictionary application or report.

Specifying the size and window location at which a column should be displayed.

We can also use extended property for SQL documentation. It requires database designer to add meaningful description for each object in the database.

Catalog view

sys.extended_properties : This will return all extended property value in the current database as given in the following definition.