Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.
I had the need recently to get put an extended property on a table in a database. I could easily have done this in SSMS, and have used the GUI before, but since I wanted to make a number of changes for testing, I wanted this done programmatically.
I knew there had to be an easy way to do this, and was hoping for an ALTER TABLE statement, but that’s not the way it works right now. There’s an sp_addextendedproperty procedure that you can use.
This procedure is somewhat of a generic procedure that takes a number of parameters, which are used to specify where the extended property applies. There is a name and value of the property, essentially a key-value pair, and then there are 3 levels of properties you can specify.
Each of the levels has a name and type as well, so this is almost like a hierarchical EAV table. It’s a bit of a mess, IMHO, but that’s OK. It’s nice to have the ability to use Extended Properties for objects, though I wish this were better implemented at different levels and embedded as a core part of your database. The levels are
- Level0 – Should be used for database scope items. For our purposes, we will use SCHEMA as the type here.
- Level1 – The next level and should be the type of object getting the property (table, view, procedure, etc.)
- Level2 – The level that gives the part of the Level1 object, i.e. COLUMN, TRIGGER, etc.
These will change, and there are some notes on BOL, so be careful and read this before you do much.
This post looks only at adding a property to a table, so let’s do that.
I want to add a property to note that a particular table doesn’t need a Primary Key (PK). To do that, I’m going to call my type [PKException] and use a value of 1 to indicate that no PK is expected on this table.
My call for the procedure will be:
EXEC sp_updateextendedproperty
@name = N'PKException',
@value = '1',
@level0type = N'Schema', @level0name = 'dbo',
@level1type = N'Table', @level1name = 'SalesTax3'
;
In this case, I have a table called “SalesTax3” and it’s in the dbo schema. Those are my values for the Level0 and Level1 parameters. I can ignore the Level2 parameter since I am specifying this as a table level property.
When I do this, I can then see the property in a few ways, but the easiest for most people is in the table properties, the Extended Properties tab,
That’s about it. If I want more properties, I can add them by changing the name and value of the property in the code above. I can also change the schema and table if I want this property added to other tables.
SQLNewBlogger
This was another side post from a separate post I was writing. I was working on solving a problem and needed an extended property. As I looked up the data to solve my issue and wrote code, I copied the Extended Property code and took a screenshot, leading to this side post.
Once I had that, this was about 15 minutes to write. I’ll publish this one first, and refer to it in the post that solved my original problem.
References
sp_addextendedproperty – https://msdn.microsoft.com/en-us/library/ms180047.aspx
sys.extendedproperties – https://msdn.microsoft.com/en-us/library/ms177541(v=sql.90).aspx
