Click here to Skip to main content
15,867,308 members
Articles / Database Development / SQL Server
Article

Towards the self-documenting database: extended properties

7 Jan 20077 min read 79.7K   23   5
With SQL Server, it is strange how some of the most radical improvements that have been introduced over the years have been slipped in almost unnoticed.

This article is in the Product Showcase section for our sponsors at CodeProject. These articles are intended to provide you with information on products and services that we consider useful and of value to developers.

Introduction

With SQL Server, it is strange how some of the most radical improvements that have been introduced over the years have been slipped in almost unnoticed. Conversely, the features that were plastered all over the marketing brochures are sometimes the ones that turn out to be dead ends.

A feature that fits into the former category is the extended properties of databases. Introduced quietly with SQL Server 2000, one could have easily missed them but they have proved, after all, to be of great value to the developer. Extended properties are the route to creating self-documenting databases. I use the term 'self-documenting' in the sense that one can attach the documentation directly to the database itself, rather than create a separate document. Basically, you use the extended properties of data objects to apply your own properties to the metadata.

Using extended properties

The classic example is to use extended properties of an object to append a description of that object, be it a trigger, stored procedure, function, table, view, parameter, index, constraint or column. One can also use extended properties to document details such as the date of revision, author, input mask, caption, history, rendering-style, and so on.

One of the classic general tricks that programmers have used in the past to add documentation to source code is to structure the comments of source code by inserting predefined markers to indicate the category of information (revision date, for example) to any application that generates the documentation (such as Javadocs). This can't be done in SQL Server as source is only maintained in the database for certain objects such as procedures and functions. Since the introduction of extended properties, such tricks would be unnecessary anyway.

The advantage of using the extended properties is that the documentation, notes, and so on stay with the databases and can be used as a "live documentation" mechanism. They are backed up with the database, and scripted out with the build scripts.

Despite their obvious utility, Microsoft has treated the feature with a curious lack of enthusiasm. There is the smell of 'wet paint' about the design. Extended properties allow you to document your database objects but it has been left to third-party utilities such as SQL Doc and DBDesc to exploit the use of these properties for generating the full documentation of the database from the database itself.

A consequence of Microsoft's indifference to extended properties is that they forgot to include them in the replication synchronisation process. You have to do it manually (a tool such as SQL Compare will synchronise them properly). Also, they neglected to provide an Information_Schema view of the extended properties, which would have made to make it easier to access them from SQL.

Another difficulty is that some third-party software vendors have used the extended properties for other purposes, such as storing parameters for entity-relationship diagrams. This makes it difficult for utilities that extract the documentation as there is no standard property name other than MS_Description.

Creating extended properties via code

Microsoft provides one extended property, MS_Description, which can be used from both Enterprise Manager and SSMS to provide a description of the object to which it is bound. Further, the Diagram Designer provides a description field, accessible via the custom view, which provides an easy way of viewing and editing the documentation of the columns.

However, extended properties are just about providing basic descriptions of objects. They are a lot more versatile that that. The designers of extended properties sensibly placed no restrictions on the properties that one could attribute to database objects. It is perfectly OK, for example, to provide extra metadata to assist the application layer in rendering or querying the data.

When writing the documentation for objects, it is generally quickest to use the facilities within Microsoft's own tools to add basic descriptions, but beyond that there eventually comes a time that one has to use stored procedures to add documentation.

At the basic level, in SQL Server 2000, all extended properties are stored in sysproperties, but are accessed by a number of stored procedures.

<><><><TD><CODE>sp_addextendedproperty </> <td>Adds a new extended property to a database object</td> </></>
sp_dropextendedproperty Removes an extended property from a database object
sp_updateextendedproperty Updates the value of an existing extended property
fn_listextendedproperty Retrieves the value of an extended property or the list of all extended properties from a database object

These stored procedures are clumsy to use and hardly encourage the programmer into using extended properties. However, a few examples (for SQL Server 2000) might make their use a bit clearer:

SQL
/*we add the extended property to provide a description to the 
  dbo.Customer.InsertionDate column   */
sp_addExtendedProperty 'MS_Description', 
               'the date at which the row was created',  
               'user', 'dbo', 'table', 'Customer', 'column', 'InsertionDate' 

-- alternative syntax for SQL 2005
sp_addExtendedProperty 'MS_Description', 
               'the date at which the row was created', 
               'schema','sales', 'table', 'Customer', 'column', 'ModifiedDate'

/* and then update the description of the dbo.Customer.InsertionDate column */ 
sp_UpdateExtendedProperty 'MS_Description', 
         'the full date at which the row was created',  
         'user', 'dbo', 'table', 'Customer', 'column', 'InsertionDate' 

/* we can list this column */ 
SELECT * FROM ::fn_listExtendedProperty  
  ( 'MS_Description','user', 'dbo', 'table', 'Customer', 'column', 
    'InsertionDate') 

/* or all the properties for the table column of dbo.Customer*/ 
SELECT * FROM ::fn_listExtendedProperty     
   (DEFAULT,'user', 'dbo', 'table', 'Customer', 'column', DEFAULT) 

/* And now we drop the MS_Description property of dbo.Customer.InsertionDate 
   column     */ 
sp_dropExtendedProperty 'MS_Description',   
'user', 'dbo', 'table', 'Customer', 'column', 'InsertionDate

A database-documenting stored procedure

I find the stored procedures described in the previous section unintuitive and don't exactly tempt the programmer into adding documentation. I'd prefer something that described the object and its hierarchy in a more conventional way. For example if one wanted to alter the description of a surname column in a Customer table then it should be 'dbo.Customer.Surname.MS_Description' using the 'user.table.column' hierarchy.

In order to make things easier, I created a simple 'helper' stored procedure which simplifies the access to Microsoft's system stored procedures, but doesn't try to replace them. If you provide the description of the object and the hierarchy, then it displays what is there. If you provide a value, it either assigns it or, if you want, appends it to the end of the current value.

The stored procedure autosenses which version of SQL Server it is on, and loads with the valid object hierarchies for the operating system. It checks the hierarchy you give it to see if it is valid. This list is rather handy, so the stored procedure also includes a feature that provides the hierarchy as a table.

The full source code for this spDBDoc stored procedure is provided in the source code for this article (simply click the "Code Download" link in the box to the right of the article title).

Create the stored procedure and let's try it out. First, let's see what hierarchies can have extended properties.

SQL
EXEC spDbDoc '','','','possible'

Or, alternatively:

SQL
spDbDoc @Function='possible'

You will see that there are rather a lot – and that the possible objects that can have attributes attached to them has been greatly expanded in SQL 2005 (those picked out in bold are available in both 2000 and 2005):

assemblySchema.Serviceuser.function.Constraint
contractSchema.Synonymuser.function.Parameter
Event NotificationSchema.Tableuser.Procedure
fileGroup.Logical file NameSchema.Table.Columnuser.Procedure.Parameter
Message typeSchema.Table.Constraintuser.Queue
partition FunctionSchema.Table.Indexuser.Queue.Event Notification
partition SchemeSchema.Table.TriggerUser.Rule
Remote Service BindingSchema.Typeuser.Service
routeSchema.Viewuser.Synonym
SchemaSchema.View.TriggerUser.Table
schema.aggregateSchema.View.columnUser.Table.Column
schema.DefaultSchema.View.indexUser.Table.Constraint
schema.functionSchema.XML Schema CollectionUser.Table.Index
schema.function.columnServiceUser.Table.Trigger
schema.function.Constrainttriggeruser.Type
schema.function.ParametertypeUser.View
schema.ProcedureuserUser.View.column
schema.Procedure.Parameteruser.aggregateUser.View.index
schema.Queueuser.DefaultUser.View.Trigger
schema.Queue.Event Notificationuser.functionUser.XML Schema Collection
schema.Ruleuser.function.column

Let's start very simply. Let's just create a description for the entire database

SQL
EXEC spdbDoc '','',
    'This is a sample database that illustrates how extended properties 
can be assigned to objects' 

Nothing exciting here so let's add a 'revision date' property:

SQL
EXEC spdbDoc '','revisionDate','20 Nov 2006: Built the first iteration' 

Now we want to add the new version, rather than replace the existing value:

SQL
EXEC spdbDoc '','revisionDate',
             '21 Nov 2006: Fixed warning message in build script','append' 

We can list all extended properties and values for columns of a given table, for example the 'customer' table:

SQL
EXEC spdbDoc 'user.table.column','dbo.customer' -- SQL 2000 
EXEC spdbDoc 'schema.table.column','sales.customer' -- SQL 2005 

Assigning a standard MS_Documentation property and value is easy:

SQL
--either alter or create an entry for the MS_Documentation property for 
--insertionDate 
spdbDoc 'user.table.column','dbo.customer.insertionDate',
        'This logs the date that the row was inserted' 

Or you can add your own property. By way of example, we assign a suggested convert style for a date field:

SQL
/* either alter or create an entry for the ConvertStyle property for 
   insertionDate */
EXEC spdbDoc 'user.table.column','dbo.customer.insertionDate.ConvertStyle',
             113 

If you take a look at the source code download you'll find several more examples for this procedure included as comments.

Of course, ultimately, extended properties are just one of the various means of ensuring that your databases are well-documented and easily understood, such as using long descriptive object names. We welcome your contributions – leave your comments below, or drop a mail to the editor (editor@simple-talk.com). The best five suggestions for ways to help make a database inherently self-documenting will receive a prize!

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here


Written By
United States United States
William Brewer is a SQL Server developer who has worked as a Database consultant and Business Analyst for several Financial Services organisations in the City of London.

Comments and Discussions

 
Questionattachment file is missing ,can you fix it? Pin
Li James19-Jun-18 15:28
Li James19-Jun-18 15:28 
QuestionLiveDoco - a tool that uses extended properties to store descriptions Pin
z102412-Apr-12 13:54
z102412-Apr-12 13:54 
Totally agree that Extended Properties are a fantastic vehicle for implementing self-documenting DBs, but not just that, they could be used to store all sorts of metadata - very similar to .NET Attributes.
Anyway, check out my new tool based on this approach - LiveDoco - a DB documentation tool/db metadata explorer implemented as a IIS Web app: http://www.livedoco.com/a-new-approach-to-documenting-databases[^]
GeneralDelete all stored procedures ext props in sql server 2005 Pin
yordan_georgiev4-Dec-08 19:57
yordan_georgiev4-Dec-08 19:57 
GeneralWeb page Pin
Jim Brooks23-Oct-07 10:48
Jim Brooks23-Oct-07 10:48 
Generalexcellent Pin
mgutierrez21-Aug-07 11:56
mgutierrez21-Aug-07 11:56 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.