DB Developer : Document Your Objects

There are some very useful features, almost unseen and forgotten, buried somewhere in the SQL Server space that can give back very significant benefits to both DBAs and DB Developers in their efforts to make databases live longer. As a database developer, you might want to document each important object in your database (e.g. each column of every table that you have) and store it somewhere. Neglegted, there is a facility where you can put those very important object notes (e.g. a column’s build history, comments) somewhere inside the database.   

It is very simple. There are two ways to document your objects. First you can use SSMS as shown in the following screens   

   

   

You can also achieve the same effect using T-SQL. To add more comments, you may use the following statement:   

EXEC sys.sp_addextendedproperty
 
@name=N’Description’,
  @value=N’This column denotes a bird”s habitat’ ,
  @level0type=N’SCHEMA’,
  @level0name=N’dbo’,
  @level1type=N’TABLE’,
  @level1name=N’mytable’,
  @level2type=N’COLUMN’,
  @level2name=N’Habitat’
GO

  

  

You can then retrive all the objects’ notes using the following statement:

SELECT objname, name, value FROM ::fn_listextendedproperty (NULL, ‘schema’, ‘dbo’, ‘table’, ‘MyTable’, ‘column’, default) 

The result would be like the following:

That’s all folks! Hope you learned something out of this! 🙂

**************************************************
Toto Gamboa is a consultant specializing on databases, Microsoft SQL Server and software development operating in the Philippines. He is currently a member and one of the leaders of Philippine SQL Server Users Group, a Professional Association for SQL Server (PASS) chapter and is one of Microsoft’s MVP for SQL Server in the Philippines. You may reach him by sending an email to totogamboa@gmail.com

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s