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.


Adding and Removing Extended Properties
Published: Jul 16, 2017
This is an obvious continuation of my previous post about Extended Properties in which I will show you how easy it is to add and remove Extended Properties from a table in SQL Server.

This is possibly one of the simplest pieces of code that I have placed on my website, but as it’s so important when designing a database I wanted to make sure that it was included. And with it being so simple there’s no excuse to not utilize this at every opportunity.

So… we’ll use the example from my previous post:

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


So how can we add the relevant pieces of information to our table? Well, we do the following:

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


It’s as simple as that. You can clearly see that the first variable is where we place our description, the second is where we place our value or identifier, and then it’s simply a matter of stating our schema, table name, and then column name.

It’s no more complex than that.

Therefore we can query this as follows:

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


Note that I have included a left join into the columns table because you don’t have to specify a column and therefore you can lose records if you insist on an inner join.

To remove extended properties we simply do the following:

exec sp_dropextendedproperty 'Default Value', 'schema', 'dbo', 'table', 'person', 'column', 'createTime'
go
exec sp_dropextendedproperty 'Primary Key', 'schema', 'dbo', 'table', 'person', 'column', 'personID'
go
exec sp_dropextendedproperty 'Natural Key', 'schema', 'dbo', 'table', 'person', 'column', 'NINumber'
go


Note that in the drop procedure we don’t have to provide a description… but all other columns are exactly as per our create statement.

Here an entire statement including both create and drop:

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

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

exec sp_dropextendedproperty 'Default Value', 'schema', 'dbo', 'table', 'person', 'column', 'createTime'
go
exec sp_dropextendedproperty 'Primary Key', 'schema', 'dbo', 'table', 'person', 'column', 'personID'
go
exec sp_dropextendedproperty 'Natural Key', 'schema', 'dbo', 'table', 'person', 'column', 'NINumber'
go

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

drop table person
go


And that’s it. That’s how easy it is to create and remove extended properties. Another reason why you should always use these as standard.
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.

Categories


© Copyright 2020 SQLTraining Ltd.