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

    SQL Prompt code analysis: avoiding the old-style TOP clause (ST006)

    The syntax of the TOP clause insists on a bracketed, or parenthesized, expression as its first argument, so that TOP 34 should, strictly speaking be TOP (34), although if you are just supplying a numeric constant, such as 34, as an argument then either style will work. Early incarnations of TOP could use only a

  • 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 to Test SQL Server Functions and Procedures using SQL Prompt

    When writing functions or procedures, a common chore is to devise and implement the tests that ensure that the routine always works as expected. The best way to do this is to define the tests in a batch that is attached to the CREATE or ALTER script. This isn’t just to prevent you from making

  • Article

    Quick SQL Prompt tip - picking the columns you need for speedy results

    One of the poor practices that I see so many people doing in code is using SELECT *. This is poor practice because it takes more time to complete for clients, it’s an unnecessary use of resources on the client, server and network, and it can fill the buffer cache with rarely-used data. For example,

  • University

    Take the SQL Prompt course

    This Redgate University course takes you from installation all the way up to getting the most out of the advanced operations in SQL Prompt. As well as autocompleting your code, you’ll learn how SQL Prompt can help you with code formatting, object renaming, code analysis, and other useful tip and tricks.

  • Forums

    SQL Prompt Forum

    Write, format, and refactor SQL effortlessly