What Are Extended Properties
Published: 3rd August 2015
Extended Properties are a feature of SQL Server which are, in my opinion at least, massively underutilized and should be embraced a lot more frequently.

These are basically meta-data that you can add to an object in order to provide information that would otherwise be hard to obtain or, at least, hard to obtain easily. And it can give you a level of information that you might not otherwise have access to.

As an example, you may have a table with the following information and schema:

if object_id('person') is not null drop table person

create table person
personID int identity primary key clustered,
firstName varchar(25),
lastName varchar(50),
NINumber varchar(15),
DOB date,
createTime datetime default(current_timestamp)

(For any Americans out there, the NI number is basically the UK equivalent of your Social Security Number)

So, how can extended properties help us? Well, let’s presume we’ve just been handed ownership of the databases containing this table and we know nothing about it… what can we do?

Firstly, we could have a look at the clustered index, the primary key and, if we were feeling adventurous, we could have a look through some system tables and find that there’s a default value and what that value happens to be.

However, would we ever know that the NI Number is actually a “natural key” of the table? That could be a VERY vital piece of information. Well, if we created the table with extended properties then this is exactly what we could know. And I’m all in favour of providing that type of information at all opportunities.

I’m not going to explain the process of adding and removing extended properties here as I’ll leave that to my next post, but I’ll at least show you what I’m talking about for this very table after I’ve added relevant meta-data:

select object_name(major_id) tableName, e.name propertyType, e.value propertyValue, c.name columnName
from sys.extended_properties e
left join sys.columns c
on e.major_id = c.object_id
and e.minor_id = c.column_id
where major_id = object_id('person')

See… that type of output would be incredibly useful to anyone coming across a table for the first time and therefore I’m definitely an advocate for people including this type of thing as part of their database design.
NB: Comments will only appear once they have been moderated.