23 June 2015
23 June 2015

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:

I used the $CURSOR$ token to place the cursor in this spot. When I start typing the name, I get this:

2015-05-26 18_13_57-Quick Extended Properties with SQL Prompt - Windows Live Writer

Hitting tab gives me this:

2015-05-26 18_14_20-Quick Extended Properties with SQL Prompt - Windows Live Writer

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.

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

SQL Prompt

Write, format, and refactor SQL effortlessly in SQL Server Management Studio and Visual Studio.

Find out more

Share this post.

Share on FacebookShare on Google+Share on LinkedInTweet about this on Twitter

You may also like

  • Article

    Finding code smells using SQL Prompt: old-style join syntax (ST001)

    SQL Prompt implements a static code analysis rule, ST001, which will check code automatically, during development and testing work, for occurrences of non-ANSI standard JOIN syntax. The “old style” Microsoft/Sybase JOIN style for SQL, which uses the =* and *= syntax, has been deprecated and is no longer used. Queries that use this syntax will

  • Article

    How to implement a SQL formatting standard using SQL Prompt v8

    With ten programmers working on the same project, how do you agree on a standard style for formatting SQL code, and then implement it consistently? One way is through draconian rules, meeting after meeting, and the occasional sacrifice of a programmer to put the fear in those who remain. If that doesn’t sound like fun,

  • Article

    Quickly rename variables all at once with SQL Prompt

    You’re going to run into situations where someone has made poor choices in naming objects, or maybe the business logic has changed and the object name no longer makes sense. Either way, easily and quickly taking care of renaming the objects within scripts can be a pain. Let’s take this code as an example: The

  • Article

    How you can improve your SQL with code analysis in SQL Prompt

    What is code analysis? Code analysis is a formal automated process of scanning a piece of software code and deducing potential problems, issues and faults that may not be apparent to programmers at first glance. These could include mistakes that are easy to make for but hard to detect (such as copying and pasting something

  • Article

    Finding code smells using SQL Prompt: the SET NOCOUNT problem (PE008 and PE009)

    SQL Prompt implements two static code analysis rules to check code for potential misuse of the SET NOCOUNT command: PE008 – SET NOCOUNT OFF is used PE009 – No SET NOCOUNT ON before DML Whenever you execute a query, a short message is returned to the client with the number of rows that are affected

  • Forums

    SQL Prompt Forum

    Write, format, and refactor SQL effortlessly