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.
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.
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