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,
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
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'
Hope this helps if you’re ever using Extended Properties on tables like the above.