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
go

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)
)
go

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


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
left
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'
go


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.

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

Categories


Archives


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