What Are Extended Properties
Published: Jul 09, 2017
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.
Leave a Comment
Your email address will not be published. All fields are mandatory.
NB: Comments will only appear once they have been moderated.

Your SQL Trainer
Kevin Urquhart

I am a SQL Server DBA, Architect, Developer, and Trainer for SQL Training. This is my blog in which I’m simply trying to share my SQL knowledge and experiences with the world.

Kevin Urquhart
SQL Server Consultant, London



Copyright © 2018 London SQL Server Training Courses @ SQL Training | Website Design From Scott Heron