Extended Properties

The documentations are an important part for every developer. In this article I am trying to discuss the self-documenting process of database by using extended properties.

Before writing this article, I read several article for various sources related to extended properties to collect facts.

Introduction

Extended properties are the route to creating self-documenting databases. That means one can attach the documentation directly to the database itself, rather than create a separate document.

Extended properties can be used in 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.

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. They are backed up with the database, and scripted out with the build scripts.

Extended properties and third-party

Extended properties allow us to document our 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.

Disadvantage of Extended properties

The common disadvantage of extended property is it is not replicated with synchronisation process. We have to do it manually by others tools like SQL Compare.

In the Information_Schema view the extended properties is not provided, which would have made to make it easier to access them from SQL.

The standard property name of the extended properties is MS_Description and it is used by third-party software vendors for other purposes, such as storing parameters for entity-relationship diagrams.

How we can use the Extended Properties

Microsoft just provides only one property named 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.

Extended properties are stored in sysproperties, and can be accessed by a number of stored procedures are mentioned bellow.

sp_addextendedproperty	Adds a new extended property to a database object
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

Example

/*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'

Hope you like it.

Posted by: MR. JOYDEEP DAS