Thursday, 14 June 2012

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.
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.

Adds a new extended property to a database object
Removes an extended property from a database object
Updates the value of an existing extended property
Retrieves the value of an extended property or the list of all extended properties from a database object
/*we add the extended property to provide a description to the
  dbo.Customer.InsertionDate column   */

'the date at which the row was created', 
'user', 'dbo', 'table', 'Customer',
'column', 'InsertionDate'

-- alternative syntax for SQL 2005

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

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

'user', 'dbo', 'table',
'Customer', 'column', 'InsertionDate'

Hope you like it.

Posted by: MR. JOYDEEP DAS

1 comment:

  1. Somehow this now seems the process/item I would not be best for our situation. We have litterally over 1,000 dbs on about 100 instances.
    our prob is cloning a db numerous times across instances for QA/DEV/TEST and many times for each programmer for various reasons. Sometimes renamed per user's need to differentiate it to similar dbs on same instance. I thought using the EProp table would allow me to document the billing dept, billing codes, billing contact person etc. for reasons of help desk and monthly billing of the run-away-animals across servers. YES we could do it when requested, but on a programmers box, he should be billed [he can create and destroy at will on his boxes] for disk, backups, and project of such db's in use!
    the ExtProp table would clone to each instance and a night job could gleen the info, but now I see from above that isn't possible. Seems I should include my own, table to be part of every database with the fields I want. THEN modify MODEL db to have such a table too. this way it gets cloned and kepts for backups/restores to other instances etc.