Tag: SQLNewBlogger

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

I’ve been working a little with extended properties, adding and updating them for various objects. However in addition to altering properties, viewing the properties on an object is often necessary. This post will look at how we get the properties in a few different ways.

The easiest way to see extended properties is to look at the properties of an object in the SSMS Object Explorer. For example, I can right click on a table in OE.

Once I click Properties, I get a dialog with a lot of items on the left. The bottom one is for Extended Properties, with a simple add/edit/delete grid. Here I can see the property(ies) I’ve added.

However this is cumbersome for me. I’d much rather find a way to query the information, which is what I need to do with an application of some sort. I’d think sp_help would work, but it doesn’t. If I run this, I get the following result sets:

• header with owner, type, and creation date.
• column list with meta data
• identity property information.
• RowGuid column information
• filegroup storage location.
• Messages with index, constraint, FK, and schemabinding relations.

Not very helpful in this case.

I do know that extended property information is in sys.extended_properties. I can query this view, which gives me some information, but I need to join this with sys.objects for easy to understand information.

This works, and this is one of the ways in which I do query properties in various tSQLt tests.

There is one other way I’ve seen to query extended properties. When perusing the BOL page for sp_updateextendedproperty, I found sys.fn_listextendedpropery. This is a DMF, a function, that you can use to query for property values. Since it’s a TVF function, I need to use it in a query as a functional object.

There are lots of parameters in this function. However you can guess what they are after working with the other extended property procedures. In fact, the first time I started this post, I was disconnected and had to experiment with the function, adding parameters until it ran without an error.

The first parameter is the name of the property. This can be NULL, in which case you’ll get all the properties that exist.

The rest of the properties correspond to the level 0, 1, 2 types and names that you are using to filter the results. This is actually a good technique to use with this function, and I’ll be using this more in the future.

SQLNewBlogger

This post followed on from the previous ones. In this case, I started this disconnected, using the knowledge I had to write the basics with SSMS and the system table. That took about 20 minutes to document and then I spent 5 minutes experimenting with the function, whose name I had on an open browser tab. Once I worked through that, I spent another 5 minutes writing.

Thirty minutes to a post. You can do this.

Reference

A few items from BOL:

sp_help – https://msdn.microsoft.com/en-us/library/ms187335.aspx

sys.extended_properties – https://msdn.microsoft.com/en-us/library/ms177541.aspx

sys.fn_listextendedproperty – https://msdn.microsoft.com/en-us/library/ms179853.aspx

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.

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

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

One of the things I needed to do recently was add some extended properties to objects. I got the idea of using them from John McClusky at SQL Bits. He had a great presentation on tSQLt that’s worth watching.

In any case, I wanted to add, and update, extended properties.  I had used SSMS to do this, but it’s cumbersome. I decided to experiment and see how the T-SQL code works. My browsing of Books Online showed me there are a few procedures used, one each for adding, updating, and deleting properties. I decided to start with sp_addextendedproperty.

This procedure takes some interesting, rather unintuitive arguments. Name and value are easy to understand. These are the name of the property and it’s assigned value. One thing to note is that value is a sql_variant, which should work fine for most situations, but CASTing may be required.

However the next arguments are level 0, 1, and 2, with a type and name for each. Those didn’t make much sense at first. In fact, as I wrote a few scripts, I had to keep looking up the meanings. Essentially we have three classifications of objects. The outer containers, the objects, and the dependent objects. I’ll explain them below.

The level0 type is essentially the class of object. Is this an Assembly, a Contract, a Schema, etc. For my purposes, this has always been a schema, but certainly you could add properties to the other classes if you needed them.

The level1 is the object type that we usually work with: table, view, function, procedure. For me this is pretty much been table, view or procedure, but certainly function is something I’d use as well.

The level2 is the dependent object: the trigger, the column, the parameter, the constraint. These I haven’t really used, but I certainly think that adding in properties for indexes, triggers, etc are valuable.

Adding a property is easy. For example, one of the items I add is a PK exception for heap tables. To do that (for the SalesHeader_Staging table), I’d run this.

EXEC sys.sp_addextendedproperty 
  @name = 'PKException',
  @value = 1, -- sql_variant
  @level0type = 'schema', -- varchar(128)
  @level0name = 'dbo', -- sysname
  @level1type = 'table', -- varchar(128)
  @level1name = 'SalesHeader_Staging' -- sysname
  ;
GO

I can see this easily in SSMS.

Properties are great ways to add additional information to an object in SQL Server, though I certainly wish they were more visible in objects.

SQLNewBlogger

I knew there was a procedure to do this, and a quick search on extended properties got me to the BOL reference. I was experimenting with adding the properties while working on this, and I had to research the meanings of the parameters a bit, so this took about 20 minutes to get ready for publication.

Reference

A few items from BOL

sp_addextendedproperty – https://msdn.microsoft.com/en-us/library/ms180047.aspx