Extended Properties: -
Basically, extended properties are used for general maintenance of instance of SQL Server. With the help of extended properties, we can create built-in database documentation for our database objects. Extended Properties allow us to add text as properties of objects in a database or of the database itself. Text can include descriptive or instructional content, purpose, input options, formatting rules etc. for a database object.
Extended properties enforce the consistency because extended properties are stored in the database, all applications reading the properties can evaluate the object in the same way.
Extended properties,
- Has a user defined name and value.
- Value of an extended property, can contain up to 7500 bytes of data.
- Multiple extended properties can be added to a single object.
- Extended properties can be defined for objects at any one of database level, schema level or object level. Here Database level treated as Level 0, Schema level treated as Level 1 and Object level treated as Level 2.
Exceptions for Extended Property: - Extended property cannot be defined on below objects:
- Full text objects.
- Object outside database scope such as HTTP end point.
- Unnamed objects such as partition function parameters.
- System defined objects such as system tables, catalog views and system stored procedures.
Below are the system procedures to interact with extended properties: -
1) To add an extended property - sp_addextendedproperty (Transact-SQL)
2) To update an extended property - sp_updateextendedproperty (Transact-SQL)
3) To delete and extended property - sp_dropextendedproperty (Transact-SQL)
4) To view an extended property –
fn_listextendedproperty (Transact-SQL) &
sys.extended_properties (Transact-SQL)
No comments:
Post a Comment