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.

pic1

Example

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

Output

pic2

Advertisements
  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 )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: