Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Creative Uses for Extended Properties in SQL Server


By:   |   Last Updated: 2011-07-11   |   Comments (1)   |   Related Tips: More > 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.


Last Updated: 2011-07-11


next webcast button


next tip button



About the author
MSSQLTips author Brent Shaub Brent Shaub has been creating, renovating and administering databases since 1998.

View all my tips




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Friday, November 18, 2011 - 11:35:22 AM - Liz Pfahler Back To Top

Great article! With some planning and diligence this could be just like JAVA Doc and we'd have self documenting code. The hard part would be the diligence of adding the comments, as we all know, but still, this is a great idea! thanks for writing it!

 


Learn more about SQL Server tools