Home > T-SQL > Extended Property – SQL Server

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.



Execute this query in a query window of SSMS

SELECT *, OBJECT_NAME(major_id) AS ‘Major Name’, OBJECT_NAME(minor_id) AS ‘Minor Name’ from sys.extended_properties



  1. No comments yet.
  1. No trackbacks yet.

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 )

Google+ photo

You are commenting using your Google+ 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 )


Connecting to %s

%d bloggers like this: