How to Use SQL Server Extended Properties to Store Metadata
Storing metadata about database objects is a common practice that provides important context about a database’s tables, columns, and other elements. In the realm of SQL Server, there is a feature called Extended Properties that allows database developers and administrators to store additional meta-information directly in the SQL Server Database Engine. This extensive guide provides a comprehensive examination of SQL Server’s extended properties, from their concept to the practical implementation.
What are SQL Server Extended Properties?
Extended properties in SQL Server are named properties that you can assign to database objects. They are essentially name-value pairs that can be added to objects in SQL Server, such as tables, views, stored procedures, and other database objects. These properties allow for storing extra information that cannot be accommodated in SQL Server’s predefined system columns.
Benefits of Using Extended Properties
Here are some benefits of using extended properties in SQL Server:
- Documentation within the database: Extended properties can be used to document database objects internally. This includes things like descriptive annotations, usage constraints and guidelines, version information, and more.
- Improved maintenance and support: As extended properties improve self-documentation, they can simplify maintenance tasks and assist new developers in understanding the database objects.
- Tool support: Certain database tools and applications can utilize the metadata stored in extended properties to provide enhanced functionality or to better interface with the database schema.
- Custom metadata: The flexibility to store custom information per environment, project, or team need, providing a more adaptable and descriptive database architecture.
Understanding the Basics of Extended Properties
The extended properties feature supports a range of operations including adding, updating, deleting, and querying these properties. Here are some key aspects:
Types of Objects: You can add extended properties to nearly all types of objects in SQL Server, such as tables, columns, indexes, triggers, stored procedures, and user-defined functions.
Size Limitations: An extended property is limited to 7,500 bytes. This includes both the name and the value parts of the property.
Multiple Extended Properties: Multiple extended properties can be assigned to a single database object, allowing a variety of metadata to be stored about any one element.
Access and Security: The accessibility of extended properties is subject to SQL Server security and permissions. Only users with appropriate permissions can read or write extended properties.
How to Manage Extended Properties
Managing extended properties requires know-how in SQL Server’s system stored procedures and functions. The following chapters will explore the key actions in detail.
Adding Extended Properties
To add an extended property, you would use the sp_addextendedproperty system stored procedure. The syntax is as follows:
EXEC sp_addextendedproperty
@name = N'Property Name',
@value = 'Property Value',
@level0type = N'Schema',
@level0name = 'Schema Name',
@level1type = N'Table',
@level1name = 'Table Name',
@level2type = NULL, --This is for sub-objects like columns
@level2name = NULL;In this example, we are adding a property to a table. If we were to add a property to a sub-object, like a column, we would include the level2type and level2name parameters.
Updating Extended Properties
Thereafter, you might need to update the value of an existing extended property. This is done using the sp_updateextendedproperty system stored procedure as shown below:
