What are Extended Properties?
It is a feature which helps in storing metadata of SQL Server objects in database. It allows you to add descriptive text, instructions, formatting rules to display a column value and input masks. Extended Properties are created as user-defined name/value pairs; managed and stored internally in the database which helps in reading and evaluating the object information the same way. You can have multiple extended properties for a single object.
How are they defined?
Extended property is defined as a user-defined name/value pair. Name property is of sysname type whereas Value property is of sql_variant type (contains up to 7500 bytes of data). The classification of the objects is done at three levels: level 0, level 1 and level 2
Level 0: Highest Level; objects that are contained at database scope.
Level 1: Objects in schema or user scope
Level 2: Objects at this level are contained by objects are level 1
For example, if we specify table column as “Schema.Table.Column”, Schema is Level 0 object, Table is Level 1 object and Column is Level 2 object. It’s important to note that while referring to child levels, we need to specify parent levels that contain the child objects. For instance, if we are adding metadata for column (at level 2), we need to specify table name (level 1) and schema name (level 0).
Which objects have extended property support?
Here is the list of objects, which have extended property support along with their correspoinding levels.
Extended properties cannot be defined for following objects:
- Objects outside database scope
- Objects not listed in above list (pretty obvious).
- Unnamed objects such as partition functions.
- System-defined objects.
This was an introductory blog for Extended Properties. In upcoming blogs, I will write about the usability aspect along with relevant examples. Stay tuned….!
SQLpathy 