Updating Extended Properties

Another post for me that is simple and hopefully serves as an example for people trying to get blogging as#SQLNewBloggers.

I wrote recently about adding extended properties. Updating them is very similar. There’s an analogous procedure called sp_updateextendedproperty that changes the value of properties.

The arguments are again, unintuitive, but the more I work with extended properties, the more comfortable I become. In this case, I have the same name and value, and then the level 0,1, 2 items with both a type and name.

I highly suggest, however, that you name your parameters, including the names in your calls so programmers running across the T-SQL aren’t depending on position for an understanding of the parameter.

If I look at the table from the previous post, I can update the value of my property with this code:

EXEC sys.sp_updateextendedproperty
  @name = 'PKException'
, @value = 0
, @level0type = 'schema'
, @level0name = 'dbo'
, @level1type = 'table'
, @level1name = 'SalesHeader_Staging' -- sysname
  ;
GO

However my property needs to exist. If I call this procedure with the wrong property, I get an error.

2015-11-02 17_25_03-Cortana

This means that you need to be sure that the property exists before you update it. Good code would have the error handling somewhere.

SQLNewBlogger

After writing the previous post, this one took only about ten minutes to do the typing. I’d been working with extended properties, so I had the code and just needed to take the screenshot.

Reference

A few items from BOL

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

About way0utwest

Editor, SQLServerCentral
This entry was posted in Blog and tagged , , . Bookmark the permalink.

One Response to Updating Extended Properties

  1. Pingback: Viewing Extended Properties for Information | Voice of the DBA

Comments are closed.