Author: way0utwest

I’m writing this post as a way to help motivate the #SQLNewBloggers out there. Read the bottom for a few notes on structuring a post.

I wrote recently about adding an extended property to a table. As part of what I was testing, I also needed to update properties, changing values back and forth. It’s fairly easy to do so, and I wanted to document this for my own reference.

The sp_updateexteendedproperty is analogous to the sp_addextendedproperty procedure. Here’s the code I used to change my property value on the table from the last post.

EXEC sp_updateextendedproperty 
@name = N'PKException', 
@value = '1',
@level0type = N'Schema', @level0name = 'dbo',
@level1type = N'Table',  @level1name = 'SalesTax3'
;

As you can see, I pass in the same parameters. The procedure then changes the parameter in the table. A quick check in SSMS will show you the values changed. In my case, I was changing the value from 0 to 1 to test a query.

The property does need to exist. If I execute this:

EXEC sp_updateextendedproperty 
@name = N'PKcheck', 
@value = '1',
@level0type = N'Schema', @level0name = 'dbo',
@level1type = N'Table',  @level1name = 'SalesTax3'
;

I get an error thrown from the database engine.

Msg 15217, Level 16, State 2, Procedure sp_updateextendedproperty, Line 112

Property cannot be updated or deleted. Property ‘PKcheck’ does not exist for ‘dbo.SalesTax3’.

This is a good way to handle this, as a TRY..CATCH can trap the error and do an insert instead of something else.

SQLNewBlogger

This was another side post from my testing of a solution. As I used this code to solve a problem, I kept a copy and made a few screenshots. This one was about 10 minutes in total.

References

sp_updateextendedproperty – https://msdn.microsoft.com/en-us/library/ms186885.aspx

sys.extendedproperties – https://msdn.microsoft.com/en-us/library/ms177541(v=sql.90).aspx