Product articles SQL Prompt SQL Code Snippets
Quick extended properties with SQL…

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

Find out more

You may also like

  • Article

    The '= NULL' Mistake and other SQL NULL Heresies

    The SQL Prompt Best Practice rule checks whether a comparison or expression includes a NULL literal ('NULL'), which in SQL Server, rather than result in an error, will simply always produce a NULL result. Phil Factor explains how to avoid this, and other SQL NULL-related calamities.

  • Article

    The Sins of SELECT * (BP005)

    If Prompt warns you of use of the asterisk, or 'star' (*), in SELECT statements, consider replacing it with an explicit column list. It will prevent unnecessary network load and query performance problems, and avoid problems if the column order changes, when inserting into a table.

  • Article

    SQL Prompt Safety Net Features for Developers

    Ever accidentally executed code while connected to the right database but the wrong server? Phil Factor describes a few common mishaps that everyone working in SSMS will have experienced, and how SQL Prompt "Tab Magic" provides insurance against their consequences.

  • 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.

  • Forums

    SQL Prompt Forum

    Write, format, and refactor SQL effortlessly