Extended Properties Are Your Friend

It’s nice to have friends, why aren’t you a friend of extended properties? They can make your like so much easier by helping to document your databases and objects.

Take a basic table create statement

CREATE TABLE [dbo].[EatSomeBoogers](

    [ID] [int] NOT NULL,

    [BgType] [varchar](20) NOT NULL,

    [Size] [smallint] NULL,

    [Viscosity] [tinyint] NULL





Pretty simple and we can infer a lot of the information about the table, but we mention size, are we measuring in inches, feet, yards?

We can add extended properties that will help to provide that sort of guidance for someone to reference later on.

EXEC sys.sp_addextendedproperty @name = N'Details',

    @value = N'Size is measured in mm', @level0type = N'SCHEMA',

    @level0name = N'dbo', @level1type = N'TABLE',

    @level1name = N'EatSomeBoogers', @level2type = N'COLUMN',

    @level2name = N'Size'


We can also add properties at the table level:

EXEC sys.sp_addextendedproperty @name = N'Purpose',

    @value = N'Holds information about all the gold digging’,

    @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE',

    @level1name = N'EatSomeBoogers'


And at the database level:

EXEC [MyDB].sys.sp_addextendedproperty @name = N'Usage',

    @value = N'Will handle all information as relates to digging' 



You can even add extended properties to other objects, like stored procedures:

EXEC sys.sp_addextendedproperty @name = N'ProcUsage',

    @value = N'Gets booger sizes and types', @level0type = N'SCHEMA',

    @level0name = N'dbo', @level1type = N'PROCEDURE',

    @level1name = N'GrabBoogers'



What’s great is that you can then quickly and easily query the extended properties for your objects:


        OBJECT_NAME(ep.major_id) AS ObjectName ,


        WHEN c.name IS NOT NULL then 'COLUMN'

        else o.type_desc

        END AS ExtendedPropertyType,

        c.name AS 'ColumnName' ,

        ep.name AS ExtendedPropertyName ,

        ep.value AS ExtendedPropertyValue

FROM    sys.extended_properties ep

        LEFT JOIN sys.columns c ON ep.major_id = c.object_id

                                   AND ep.minor_id = c.column_id

        LEFT JOIN sys.objects o ON ep.major_id = o.object_id

ORDER BY ObjectName, ColumnName




This give you a really quick and easy way to document your objects. I highly recommend that during your next development project that you make life easy for yourself and add extended properties to your objects (and trust me, your DBA will thank you).

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