Quick extended properties with SQL Prompt
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.
Adding 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:
|
1 2 3 4 5 6 |
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.
Updating Properties
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.
|
1 2 3 4 5 6 |
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.
Tools in this post
You may also like
-
Article
Sharing SQL Prompt Code Snippets and Styles via the Redgate Platform
SQL Prompt users can now share formatting style or code snippets in designated Team spaces on the Redgate Platform. Louis Davidson explains how it works.
-
Article
Why you Should Avoid Using the @@IDENTITY Function
Phil Factor demonstrates why SQL Prompt has a 'Best Practice' rule (BP010) that checks for use of the @@IDENTITY function, and suggests less error-prone ways to get the latest identity values used in a table.
-
Article
Problems Caused by Use of the SQL_VARIANT Datatype
Phil Factor illustrates the 'quirks' of the SQL_VARIANT datatype and why it's best to investigate when SQL Prompt alerts you to its use. It is only safe to store data as a SQL_VARIANT, if you explicitly convert it to its true type before you use it.
-
Article
Changing SET options in a Procedure or Trigger leads to Recompilation (PE012)
Phil Factor delves into SQL Prompt's performance rule, PE012, which will advise you if it detects the use of the SET statements within a stored procedure or trigger, which might cause unnecessary recompilations, though the issue extends to other types of batches.
-
