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.

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
-- https://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.