Worth Corner, Crawley, RH10 7SL

Support Hours - Mon-Sat, 8.00-18.00

Welcome to our SQL Server blog

This blog was created in order to pass on some of our knowledge and advice in the hope that the community might find it useful.

Obviously Microsoft SQL Server is our area of expertise and therefore this is our most thorough blog with everything from the basic through to the the incredibly advanced and, as such, we should have something for everyone within our pages.

Querying Extended Properties
Published: Jul 23, 2017
Now you know all about Extended Properties, I’ll provide a quick script that allows you to query them effectively and quickly.

To be honest you could get away with using the scripts that I’ve shown in the previous couple of posts, but they’re a little fluffy and it’s better to be thorough. It’s also useful when you find that sys.extended_properties uses column names that are, quite frankly, ridiculous in comparison to every other system table in SQL Server.

So what are these column name issues?

Well, firstly we have the fact that for some reason Microsoft thought it would be sensible to use MAJOR_ID instead of OBJECT_ID and that they thought MINOR_ID was a perfectly valid alternative to COLUMN_ID.

Now I know that you can add extended properties to other objects and therefore, to be honest, these aren’t as ridiculous as I’m claiming… but I’ve only ever really used these for tables and therefore it is a bug-bear of mine simply because I have the habit of typing object_id without thinking and then realizing that it doesn’t exist.

So, to be honest, it’s more my problem than theirs… but still…

Anyway, this is, for a table, the nicest way to query extended properties…

Here’s the table we’ll use:

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 constraint df_createTime default(current_timestamp)

exec sp_addextendedproperty 'Default Value', 'current_timestamp', 'schema', 'dbo', 'table', 'person', 'column', 'createTime'
exec sp_addextendedproperty 'Primary Key', 'Identity', 'schema', 'dbo', 'table', 'person', 'column', 'personID'
exec sp_addextendedproperty 'Natural Key', 'NI Number', 'schema', 'dbo', 'table', 'person', 'column', 'NINumber'

And here’s my query:

select s.name schemaName, o.name objectName, e.name extPropertyName,
e.value extPropValue, c.name columnName
from sys.objects o
join sys.schemas s
on o.schema_id = s.schema_id
join sys.extended_properties e
on e.major_id = o.object_id
join sys.columns c
on e.minor_id = c.column_id
and e.major_id = c.object_id
where s.name = 'dbo'
and o.name = 'person'

Hope this helps if you’re ever using Extended Properties on tables like the above.
Leave a Comment
Your email address will not be published. All fields are mandatory.
NB: Comments will only appear once they have been moderated.

SQL  World  CEO
Kevin  Urquhart

iPhone Selfie

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


© Copyright 2020 SQLTraining Ltd.