Fun with Extended Properties in SQL Server 2008

Most technical professionals don’t seem to enjoy writing and maintaining documentation for their databases. I know that documentation falls pretty low on my favorites list.

One pretty easy way to include some “built-in” database documentation for your databases (which could be the beginnings of a data dictionary) is to add extended properties on your objects. In this case, I want to add an extended property called MS_Description to each user table, which will hold a short description of the table.

Extended properties are exposed in the SQL Server Management Studio (SSMS) GUI, so if you are a glutton for punishment, you can do lots of clicking and typing.

image

You can also call one of three system stored procedures to manipulate your extended properties. These are:

sys.sp_dropextendedproperty

sys.sp_addextendedproperty

sys.sp_updateextendedproperty

Unfortunately, if you try to drop or update an extended property that does not exist, you will get an error. You will also get an error if you try to add an extended property that already does exist, so you need to be careful.

I wrote a quick and dirty routine that tries to make this process a little more reliable. My code block also shows some examples of how to call the system stored procedures, and how to query the extended properties in your database.

-- Fun with Extended Properties in SQL Server 2008
-- Glenn Berry 
-- January 2011
-- http://sqlserverperformance.wordpress.com/
-- Twitter: GlennAlanBerry

-- Switch to your database!
USE YourDatabaseName;
GO

-- Samples of calling system stored procedures to
-- manipulate extended properties
-- The @level1name parameter is the table name, which you 
-- should change to the table you are documenting

-- Drop an extended property
-- (Does not work if extended property is not present)
EXEC sys.sp_dropextendedproperty 
     @name  =N'MS_Description', 
     @level0type = N'SCHEMA', 
     @level0name = N'dbo',
     @level1type = N'TABLE',
     @level1name = N'ActivityEventRecipient';
GO    

-- Add the extended property
-- (Does not work if extended property is already present)
EXEC sys.sp_addextendedproperty 
     @name  = N'MS_Description', 
     @value = N'Short table description', 
     @level0type = N'SCHEMA',
     @level0name = N'dbo', 
     @level1type = N'TABLE',
     @level1name = N'ActivityEventRecipient';
GO

-- Update the extended property
-- (Does not work if extended property is not present)
EXEC sp_updateextendedproperty 
     @name  = N'MS_Description', 
     @value = N'Short table description', 
     @level0type = N'SCHEMA',
     @level0name = N'dbo', 
     @level1type = N'TABLE',
     @level1name = N'ActivityEventRecipient';
GO

-- Routine to add or update an extended property on a table
DECLARE @TableName SYSNAME = N'ActivityEventRecipient';   -- Name of table
DECLARE @MS_DescriptionValue NVARCHAR(200); 
SET @MS_DescriptionValue = N'Short table description';    -- Short description
DECLARE @MS_Description NVARCHAR(200) = NULL;

SET @MS_Description = (SELECT CAST(Value AS NVARCHAR(200)) AS [MS_Description]
FROM sys.extended_properties AS ep
WHERE ep.major_id = OBJECT_ID(@TableName)
AND ep.name = N'MS_Description' AND ep.minor_id = 0); 

IF @MS_Description IS NULL
    BEGIN
        EXEC sys.sp_addextendedproperty 
         @name  = N'MS_Description', 
         @value = @MS_DescriptionValue, 
         @level0type = N'SCHEMA',
         @level0name = N'dbo', 
         @level1type = N'TABLE',
         @level1name = @TableName;
    END
ELSE
    BEGIN
        EXEC sys.sp_updateextendedproperty 
         @name  = N'MS_Description', 
         @value = @MS_DescriptionValue, 
         @level0type = N'SCHEMA',
         @level0name = N'dbo', 
         @level1type = N'TABLE',
         @level1name = @TableName;
    END
GO
-- End of routine
     
    

-- Get MS_Description value for one table in current database
SELECT CAST(Value AS nvarchar(500)) AS [MS_Description]
FROM sys.extended_properties AS ep
WHERE ep.major_id = OBJECT_ID(N'ActivityEventRecipient')
AND ep.name = N'MS_Description' AND ep.minor_id = 0;

-- Get all MS_Description values for all tables in current database
SELECT OBJECT_NAME(ep.major_id) AS [ObjectName],
CAST(Value AS nvarchar(500)) AS [MS_Description]
FROM sys.extended_properties AS ep
WHERE ep.name = N'MS_Description' AND ep.minor_id = 0;


-- Get extended properties for all tables in one schema
SELECT objtype, objname, name, value
FROM fn_listextendedproperty (NULL, 'schema', 'dbo', N'table', default, NULL, NULL);

Of course for this to actually be valuable, you actually have to add the extended property to each table (along with anything else you want to add), and maintain it over time. You can also document the columns in your tables, and many other objects in your databases.

About these ads
This entry was posted in SQL Server 2005, SQL Server 2008, SQL Server 2008 R2 and tagged , . Bookmark the permalink.

6 Responses to Fun with Extended Properties in SQL Server 2008

  1. David Walker says:

    One of our databases has some MS_* extended properties. Do you know where these come from? For example, MS_TableMaxRecords.

    Why would a user-defined table description name start with MS_? I think this is a good place to put a description of each table, but I wouldn’t name it MS_Description.

  2. Pingback: T-SQL Tuesday #21 – Temporarily permanent hacks | David Curlewis

  3. Pingback: MS_Description and description on design grid - dBforums

  4. The real fun with extended properties is the fact that it does matter how you add them when the property is MS_Description.

    There are 3 ways. 1st: Manually, on SQL Server Form, 2nd: Scripting: the one you just have described and 3rd: manually on design view of table (column descriptions).

    Now the funny thing is that when you add a column description, it is an MS_Description property created.
    When an MS_Description property is created for the column by script, you will not see it on the table design view and if you try to enter something there it results in error, as there is already a property added…
    How can SQL server behave differently, when it is the same property?

    Also, 3rd party applications sometimes can’t see column descriptions unless they are entered through the table design view….

    Weird. Wonder if you have any idea why.

  5. Ben Fair says:

    To avoid errors, you can use the sys.extended_properties system view to check for the existence or non-existence of an item before performing an action on it.

  6. Pingback: SQL: Fun with Extended Properties in SQL Server 2008 | mytechconnect

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