The Wit and Wisdom of Extended Properties

Extended properties are an arbitrary name/value string pair that you can add to any SQL Server object: database, schema, table, column, constraint, trigger or index. Most resources suggest they are useful for either documenting your database (“here’s what this table is used for”), or adding display-specific data for a column (e.g. a regex mask for a phone number field).

Usage

Let’s say I’ve a table called Car, defined as:

CREATE TABLE dbo.Car
(
	MakeID INT NOT NULL
	,ModelID INT NOT NULL
	,[Description] VARCHAR(255) NOT NULL
	,CONSTRAINT PK_Car PRIMARY KEY CLUSTERED (MakeID, ModelID)
	,CONSTRAINT FK_Car_MakeID FOREIGN KEY (MakeID) REFERENCES dbo.Make(MakeID)
	,CONSTRAINT FK_Car_ModelID FOREIGN KEY (ModelID) REFERENCES dbo.Model(ModelID)
)

I can add a piece of data (I’ve named it “Usage”) that explains where/how the Description field is to be used:

EXEC sp_addextendedproperty 
	@name = N'Usage' 
	,@value = N'Holds the paragraph of text that is displayed in the listings page, and on the mobile site.'
	,@level0type = N'SCHEMA', @level0name = 'dbo'
	,@level1type = N'TABLE',  @level1name = 'Car'
	,@level2type = N'COLUMN', @level2name = 'Description'
GO

Click here for the full MSDN definition of sp_addextendedproperty.

I can get the values back out by using fn_listextendedproperty:

SELECT
		objtype, objname, name, value
	FROM sys.fn_listextendedproperty (NULL, 'SCHEMA', 'dbo', 'TABLE', 'Car', 'COLUMN', 'Description')
GO

which returns:

objtype      objname              name                 value
------------ -------------------- -------------------- --------------------------------
COLUMN       Description          Usage                Holds the paragraph of text ...

As mentioned previously, I can even add properties to a constraint. So why does my PRIMARY KEY look like it does..?

EXEC sp_addextendedproperty 
	@name = N'Explanation' 
	,@value = N'We used a composite primary key because, e.g., both Bentley and Lincoln have models called the Continental.'
	,@level0type = N'SCHEMA', @level0name = 'dbo'
	,@level1type = N'TABLE',  @level1name = 'Car'
	,@level2type = N'CONSTRAINT', @level2name = 'PK_Car'
GO

SELECT
		objtype, objname, name, value
	FROM sys.fn_listextendedproperty (NULL, 'SCHEMA', 'dbo', 'TABLE', 'Car', 'CONSTRAINT', default)
GO

To see all the extended properties for the current database:

SELECT * FROM sys.extended_properties
GO

which returns:

class class_desc           major_id    minor_id    name                 value
----- -------------------- ----------- ----------- -------------------- ------------------------------
1     OBJECT_OR_COLUMN     1269579561  3           Usage                Holds the paragraph of text...
1     OBJECT_OR_COLUMN     1285579618  0           Explanation          We used a composite primary...

If I wanted to build some “auto documentation” for a database, I could create a Sweave document that retrieved the properties (via RODBC, for example), and displayed them nicely as \LaTeX !

What other uses do you know of for extended properties? I’d love to hear from you in the comments below.

[Bonus tip while I’m thinking about documentation: Create a schema called ‘aaa’ (hence it’ll appear at the top of the list of tables in SSMS); add a table called aaa.README, and fill it with everything a fellow developer or analyst might want to know. Furthermore, it always helps to have a properly maintained changelog table, e.g. aaa.CHANGELOG, in addition to your usual change/control documentation.]

Advertisements

, ,

  1. Leave a comment

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

%d bloggers like this: