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:
So how can we add the relevant pieces of information to our table? Well, we do the following:
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:
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:
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:
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.
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)
)
goSo 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'
goIt’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')
goNote 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'
goNote 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