Skip to content

Adding Extended Properties to a Table

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 = '0',
@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,

2015-05-26 09_55_09-Table Properties - SalesTax3

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.


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.


sp_addextendedproperty –

sys.extendedproperties –

Our Meeting and The Day Out

Last week, Redgate Software had a large company meeting and our annual Day Out, with myself and a few dozen of our US employees in the UK for meetings and the event. It was fun, and a good time. I haven’t been to a company meeting live in 6 or so years and I’ve never been to a day out, so I was excited to go.

I was asked to do the opening at the company meeting. Normally our CEO, Simon Galbraith, does an opening and introduces new starters. However with all the US people over, I got to get up first and try to inspire, motivate, and entertain a bit.

Photo Jun 18, 7 04 46 AM

Our servery, the canteen where we eat breakfast and lunch was pretty packed on Thursday. I got up and had some fun, showing some images from the ranch of my hard work.


And telling all the youngsters that the worst job in the world was somewhere out there, but not at Redgate.


I think I motivated people a bit and got them excited about the company. That was part of the meeting, and I truly believe that. Of the 10 or so companies I’ve worked for, this is the best one for a lot of reasons, but mostly because people are respected and get to contribute. We have opportunity and responsibility, and it’s a good mix.

Afterwards we had a Nerf war in the two story atrium, which delayed a meeting for me, however it was worth it to watch the festivities.

Photo Jun 18, 8 22 46 AM

Photo Jun 18, 8 24 15 AM

Everyone was well armed from the Gun Shop.

Photo Jun 17, 9 47 41 AM

The Day Out

The Day Out is really a teambuilding event. I got out of a taxi at the Cambridge Rugby Club to see all kinds of tents and apparatus set up.

Photo Jun 19, 8 49 16 AM

We divided up into teams of 7-8 people and set about competing against each other. We had some mental events, like safe cracking.

Photo Jun 19, 4 13 50 AM

Along with a puzzle competition where we had to use the equipment outside the striped tape to get the black box out of the middle, without stepping inside. That was challenging, and with another team trying, we couldn’t do it.

Photo Jun 19, 4 15 09 AM

There were physical events as well. We had a sweeper (like on Wipeout) that we competed against and our team tied.

Photo Jun 19, 4 43 02 AM

We also had a tug of war, with two of us tied together with a bungee cord. We had to race in opposite directions for about 20-30 ft. and grab a plastic brick from an inflated stand. I have to say that I lost 2/3 chances I had, but I was proud of the one I did win. My competition was a new salesperson, Colin Tanigawa, who just graduated from the University of Washington. He’s 23, and he played football there.


Needless to say, I’m more than twice his age, so I was glad to take one from him. I got to go a second time, as the other team had an extra person, and I raced against a developer. My team leader got a sequence shot of my on the second try, when we broke the bungee. Fortunately I’d won both, so that was that.


We had lunch, competed more, in ax throwing, dog agility, and more. All in all, a fun day capped by a BBQ. My wife joined me and we relaxed and chatted with people into the night.

I had a good time with my team, and my leader, Charlotte, shown below.

Photo Jun 19, 5 02 16 AM

I’m hoping to go back again for another day out.

Skill Supply and Demand

Across my career, I’ve seen many managers and executives promote and press for IT groups to consider alternative technologies. The mass media has often promoted new platforms and languages as a more efficient way to build software. Some of their claims might be true, but I think there’s a much bigger factor than technology in how well your software is written.

Your employees’ skills are probably the most important influence on the quality of your systems. The technology doesn’t matter if the staff doesn’t understand it. I think it’s even more important to be sure that you can replace your staff. People will come and go in your organization.

I was reading about the development of RavenDB, which is a another platform. It’s written in C#, and the maintainer of the project was asked about moving to F#. His answer, which I found fascinating, was about the supply of F# programmers, both in numbers and cost. There is a lot of debate in the comments, and I find it interesting how differently many people see the world with regards to this issue.

I think about this as we look at NoSQL platforms, or even other RDBMSes. How easy would it be to dramatically port your software to another data store? Probably not easy, and not inexpensive. The time for training and rewriting code would be very expensive, so much so that it’s often not worth the effort. However the more esoteric your choice of platform, the more difficult it can be to find staff that can even manage your system.

I don’t think any of the main RDBMSes have a shortage of talented people, but I do think that it can be hard to find employees in some of the NoSQL systems. While I think most smart people could learn a new system, it has to be worth it to your organization to pay someone to learn a new skill.

Steve Jones

The Voice of the DBA Podcast

Listen to the MP3 Audio ( 2.7MB) podcast or subscribe to the feed at iTunes and LibSyn.

I have an ask…

I heard this quite a bit recently while up in Redmond.

"I have an ask for you"

"Do you have an ask?"

and more. It’s annoying, and disturbing. Hearing that distracts me from the conversation taking place. I keep wanting to say “do you mean you have a request?”

Even Microsoft employees don’t love it. However, the usage is not necessarily incorrect, which surprises me. And further annoys me.

I’m sure there’s no way to stop it, but it reads like poor choice of words, picked to seem cool or hip.

It’s not for me, and it doesn’t encourage me to do anything for you.

The Test of Time

This editorial was originally published on June 25, 2011. It is being re-run as Steve is out of town.

IBM turns 100, and in , they brag about bench pressing more other companies half its age. That’s a great headline, and I still remember when IBM was not only the premier hardware vendor of the computer world, but also the largest software maker as well. The “Smarter Planet” ads that IBM puts out there are great, and they are a diverse company, covering so many different industries and locations that it’s hard to comprehend everything they are working on.

The culture and mission of IBM has changed in 100 years, but it’s still amazing that the company has survived. I think back to the tech companies of my youth, and many of them are gone. Who remembers 3COM? Anyone set up a Wellfleet router? DEC becoming Compaq, and now HP. Apple is still around, but Sun Microsystems is disappearing into Oracle. Microsoft is only 30 years old.

I once worked for a hundred year old company, one that embraced and remembered it’s history, even as it evolved in the modern world. The President of the company had worked there for his whole career, taking over for his father that had worked there almost his whole life and still came into the office a few days a week at age 80. There was a sense of history, tradition, and pride that kept people working there, with the vast majority of the company having worked there over a decade when I was hired.

The modern world is fast paced, rapidly changing, and evolving quickly, but there is something to be said for adapting to fit in, while maintaining your mission and ideals. I hope that more companies would look beyond their next quarter or year and make the attempt to build a business that they are proud of, and can survive a hundred years into the future.

Steve Jones

Does It Count?

This editorial was originally published on Jan 28, 2011. It is being re-run as Steve is out of town.

I wrote a blog recently about downtime and SLAs, and the need to have not only downtime SLAs, but also a data loss SLA. Thanks to Paul Randal (blog | @PaulRandal) for clueing me in to the need for having both. In the comments someone mentioned that scheduled downtime ought not be counted against your uptime measurements. That’s a debate I’ve had in the past with employers, and I thought it would be a good Friday poll:

Should scheduled maintenance be counted against your downtime SLA?

There’s an argument that says since the outage is scheduled, and people are informed, that this shouldn’t really count against your work in keeping servers running. The flip side is that when the database instance is down, it’s not useable, and of limited use to the business.

I know that companies that calculate the usage of their vehicles, and maintenance counts against usage time. While it’s necessary, the idea is that mechanics should be looking to minimize it, and perhaps ensure that extra checks are done when the vehicle is being worked on to help prevent future issues. A similar argument could be made for database servers.

Do you feel that scheduled maintenance is downtime? Is it calculated that way at your employer? Let us know this Friday.

Steve Jones

More Regulation Coming?

This editorial was originally published on Jun 22, 2011. It is being re-run as Steve is out of town.

Recently Citibank had hackers access a large number of credit card numbers and account details for their customers. It was all over the news, but there was a great piece in the New York Times (registration required) about how this seems to be a nagging problem. We’ve had data breaches before, and banks have had security issues, but they don’t seem to be learning. A number of government officials in the article are quoted as seeking new regulations for data security.

As data professionals, we are often charged with ensuring data is safe, yet accessible to authorized individuals. That responsibility can be hard to manage, especially when our management often doesn’t want to increase our budgets, or accept more restrictive ways of authenticating users or even slower access. I certainly don’t envy the IT folks working at banks and healthcare companies, and am not sure I’d even want to take a job in those industries in the future.

I don’t think that more regulation is necessarily the answer, at least not direct regulation. Technology changes so quickly, systems are implemented in diverse ways, and direct regulation often leads to rules that exist to help some vendor profit, not provide better security.

Whether we get new regulation or not, I think that most of us need to learn more about what security methods work well in our systems, the implications of picking a specific type of encryption, and in which ways we can be audit our systems to detect issues. In the days of limited budgets and apathy from management, a little education is the best way to improve the security of your systems.

Steve Jones

The Company Meeting

I’m leaving today for a trip to Cambridge, UK. I do this 2-3 times a year, but this one is a little different. I’m being joined by most of my American colleagues at Redgate, all of whom are being flown over to attend a company meeting and a company day out on Friday. It’s one of the rare chances, and quite possibly the only one that will happen again, to get everyone together in the company.

It’s an expense, arguably an unnecessary one, but it’s also a treat. Spouses and partners have been invited to come along and participate in some fun on Friday. It’s something that stands out in my mind, and it solidifies that Redgate is a great company.

It’s the kind of company I’d want to build, and it’s run as I’d want to run a company.

That might be the highest complement I can pay to an employer. They run a company as I would want to do so. They treat people fairly. They treat people with respect. They try to treat customers well, as well as participate in the community. The company is not perfect, but they acknowledge mistakes and try to do better. They work to make a profit, but profit isn’t the most important thing. Perhaps best of all, the management work alongside employees and set good examples. They don’t treat themselves as above or more important than the other employees.

I’ve worked at a lot of companies in my career, and almost none of them have been companies that I thought were well run with respect for employees balanced with the profit motive and participation in the community. Most were unbalanced in some way, often with respect to profit meaning much more than individuals.

I’m proud to work at Redgate and am looking forward to the trip with my wife, and a nice weekend in Dublin.

See Two Queries at Once in SSMS

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.

One of the things that I’ve struggled with a bit in SSMS is sometimes comparing the results of two batches. I’m sure many of you have executed a query, then make a change, and execute it again, losing your results. Or you are testing something in two query windows and need to switch back and forth. Sometimes doing this, and only seeing one set of results (or checking if a query is finished) is cumbersome.

A few years ago I was watching Brent Ozar tune queries at an event and one of the things he wanted to do was compare two queries and their execution plans. He used vertical tab groups, which is a great way of seeing two things at once.

Here’s how my screen ended up during the comparison I was actually doing of three queries. I was checking credentials using a before, after, and with the DAC.


Things are a bit shrunk down as I wanted the image to be viewable. I actually had this full screen on a 30" wide monitor, and I could more easily see the queries and results from each window.

The easiest way to do this is to start with a query:


I want to change something, add a login, and test again, but I don’t want to lose my results. I’d also like to do an easy comparison. What I can do is go to the Window menu and get a new Vertical Tab Group. I could also do a Horizontal one, but comparing results is easier for me with vertical ones.


Once I select this, my selected window will move to a new tab group, and I’ll see two places where I can run code and visualize the queries and results at once.


I selected the left hand query, then clicked "New Query" to get a blank window. I then cut and pasted my code from the right to the left. This is exactly what you might want to do when tuning queries, keeping the original on the right while you work on the left.


Now I have two places to work on code. In my case, I wanted the before and after view of Server_principals as I checked some admin changes. I could do things and keep re-running the query in one of these windows, but keep the results from the other one visible.



Once again I was doing something else and realized the vertical window trick was handy. I killed the three windows I had, set up a new query, shot the screen, ran through the process with more screen shots.

Less than 10 minutes.


Watch Brent Tune Queries –

Five Minute Refactoring

I’ve listened to, and been a part of, no shortage of arguments about the technical merits of a particular programming solution. I’ve seen two (or more) developers argue about the way to solve a particular problem for hours, even days at times. The Internet is full of discussions and debates in forums on the “best” way to write code for all sorts of specific issues.

These arguments are sometimes referred to as religious wars for the devotion and belief that each proponent has in their solution. I tend to try and avoid becoming embroiled in any sort of zealotry as I am always cognizant of the fact that I need to be effective. I need to get things done and can’t spend a lot of time arguing about a solution, whether for or against it. i need to get work completed.

When I was managing people I tried to limit the devolution of my staff into a never ending argument, but I ran across a better solution recently. Al Shalloway wrote a blog about limiting discussions to five minutes. He notes that two level-headed people ought to be able to come to a consensus in five minutes. Beyond that he says it’s likely that one of them is arguing about covering possibilities rather than current realities.

That’s interesting, and like many rules, I’m sure there are exceptions. A manager can make a decision to continue a discussion if they really think that it is warranted. However as a default, the idea that a team should choose the easiest solution to implement if they can’t decide makes sense. It’s the least amount of work that might be wasted, and we want to avoid wasting time and work when we can.

We won’t always agree, and despite our best intentions, we will make mistakes and we will make poor decisions. Don’t let indecision continue indefinitely and don’t rashly make decisions, but don’t let the fear of mistakes prevent you from choosing a path. Be decisive and move forward, adjusting as you learn more that helps you refine your solution.

Steve Jones

The Voice of the DBA Podcast

Listen to the MP3 Audio ( 2.5MB) podcast or subscribe to the feed at iTunes and LibSyn.


Get every new post delivered to your Inbox.

Join 5,308 other followers