I’ve been experimenting with Extended Properties, and I found myself slightly annoyed by the syntax of adding and updating Extended Properties. I decided to take advantage of SQL Prompt to store the commonly used code for adding and updating properties.
I’m a big fan of naming the snippets the first thing that comes to mind. This is because the short, three character abbreviations get confusing (to me) when so many are close together. Even the st100 snippet hasn’t registered with me and I always edit “ssf” on a new install.
In my case, I was adding a specific property for various tables. Rather than trying to make this too generic, I added a snippet with the name “addPKException” that has this code:
EXEC sp_addextendedproperty @name = N'PKException', @value = '1', @level0type = N'Schema', @level0name = 'dbo', @level1type = N'Table', @level1name = '$CURSOR$' ;
I used the $CURSOR$ token to place the cursor in this spot. When I start typing the name, I get this:
Hitting tab gives me this:
My cursor is right where the table name goes, and I can just type the table name and execute the code.
I use almost the same code for an update. I named this one updatePKException. In this case, the only real difference is the code has the sp_updateextendedproperty as the call, and I need to change two values. Since the table name is definitely going to be changed, I leave the cursor there.
EXEC sp_updateex tendedproperty @name = N'PKException', @value = '1', @level0type = N'Schema', @level0name = 'dbo', @level1type = N'Table', @level1name = '$CURSOR$' ;
Prompt is Handy
Both of these snippets are really handy. If I need to add an Extended Property for anything else, I can easily add another snippet, which is what I’d prefer rather than making this one more generic. If I had a lot of “update” snippets, I might shorten to upd or something similar to reduce typing.
This is one of those integrations with the way I work with SQL Prompt that makes my typing and coding effectiveness so smooth and fast. By adding new snippets that are intuitive and make sense, I can adapt SQL Prompt to the ways I work.