Creative Uses for Extended Properties in SQL Server

By: Brent Shaub   |   Updated: 2011-07-11   |   Comments (1)   |   Related: > Database Design

Problem

Systems could often have volumes of documentation written about them because of their complexity, the extensive changes over time and the many minds that worked on them throughout their existence. Because of this, documentation can get scattered and outdated, and finding the appropriate information bears the risk of losing time sifting through the many folders and files that may exist.  In this tip we look at how to leverage Extended Properties to document your database.

Solution

The use of Extended Properties allows you to store a property name and a value for just about anything you want to document within SQL Server. By creating some simple naming conventions, extended properties can be used to create inline system documentation for both developers and end-users. In a previous tip, I discussed how to create user friendly column names using extended properties and in this article I will scratch the surface and explore two uses for extended properties: Developer Comments and Data Entry Assistance.

Developer Comments

Creating In-Line Developer Comments

create table page (
  page_id int identity(1,1) not null,
  page_url varchar(50) not null,
  constraint PK_page primary key (page_id)
)
exec sp_addextendedproperty
@name = N'Dev Comment', 
@value = 'URL after first ''/'' and up to but not including ''?''',
@level0type = N'schema', @level0name = dbo,
@level1type = 'table', @level1name = page,
@level2type = 'column', @level2name = page_url
exec sp_addextendedproperty
@name = N'Dev Process', 
@value = 'Each time a new page is added to the site, a new record needs inserted here.',
@level0type = N'schema', @level0name = dbo,
@level1type = 'table', @level1name = page

This first example creates an extended property for the column page_url in table page.

For the first extended property, the developer wants everyone to know the stored value should not include the site's host name, the preceding protocol (http) or any values after the query string. Front-end programmers would also benefit knowing this when they need to save URLs.

The second extended property is used as a reminder that any new URL that gets added to the site will need a record added to this table as well. By leaving out the level2 parameters, the comment applies to the table instead of a specific column.

The words "Dev Comment" and "Dev Process" are yours to decide just like the values that you store.

Form Design

Creating In-Line Form Design Comments

create table person (
  pers_id int identity(1,1) not null,
  pers_fname varchar(50) null,
  pers_lname varchar(50) null,
  pers_ssn varchar(11) not null, -- will store the mask in the data in this example
  constraint PK_pers primary key (pers_id)
)
EXEC sp_addextendedproperty 
@name = N'Val Label', 
@value = 'Social Security Number',
@level0type = N'Schema', @level0name = dbo,
@level1type = N'Table',  @level1name = person,
@level2type = N'Column', @level2name = pers_ssn;
GO
 
EXEC sp_addextendedproperty 
@name = N'Val Message', 
@value = 'Please use this format for Social Security number: ###-##-####',
@level0type = N'Schema', @level0name = dbo,
@level1type = N'Table',  @level1name = person,
@level2type = N'Column', @level2name = pers_ssn;
GO
EXEC sp_addextendedproperty 
@name = N'Dev Comment', @value = 'Is it keen to store the input mask in the value?',
@level0type = N'Schema', @level0name = dbo,
@level1type = N'Table',  @level1name = person,
@level2type = N'Column', @level2name = pers_ssn;
GO

For this table, we'll focus first on the two Val comments. These both pertain to the same field: pers_ssn. The first extended property creates a user-friendly label to show on the form, presumably near its input box. The second is intended to be an alert() to inform the user that there's a certain format this value needs to adhere to. 

The third property is just a developer note about how the value should be stored.

Retrieving Extended Properties

Retrieving these extended properties can be done using a simple query that uses a few system tables as shown below.

Retrieving Extended Properties

select sysobj.[name] as objname, 
 syscol.[name] as colname, 
 sysexp.[name] as propname, 
 sysexp.[value] as propval
from sys.extended_properties sysexp
 inner join sys.objects sysobj on sysobj.[object_id] = sysexp.major_id
 left outer join sys.columns syscol -- when expprop is on object, minor_id = 0
  on syscol.[object_id] = sysobj.[object_id] 
  and syscol.[column_id] = sysexp.minor_id
where sysexp.class = 1 -- 1 = object or column
     

All the values were displayed to give an idea of how multiple tables appear together, but filters could be used as follows:

For the Developer Manual you could filter on propname by adding something like this to the query:

and propname like 'Dev%'

For the Form display you can filter on a table or column by adding something like this to the query:

and propname like 'Val%'

Next Steps

• Get cracking on migrating sticky notes, one-off reminders and Things You'd Want Someone Else to Know into the database using Extended Properties
• Build a report that outlines Developer Comments and share with your team how to keep them up-to-date.
• Experiment with migrating form field labels and validation messages into comments, if not to build in HTML, at least for centralized reference.
• Review the syntax and options in this previous tip on Extended Properties.
• Determine ways that your environment can utilize this feature of SQL Server and post them in this article's comments.

About the author

Brent Shaub has been creating, renovating and administering databases since 1998.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips