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

    Refactoring Databases with SQL Prompt

    Louis Davidson demonstrates how SQL Prompt can significantly lessen the pain involved in occasional, 'heavyweight' database refactoring processes, such as renaming modules, tables and columns (Smart Rename), or splitting tables (Split Table).

  • Article

    When to use the SELECT…INTO statement (PE003)

    SELECT…INTO is a useful shortcut for development work, especially for creating temporary tables. However, it no longer has a clear performance advantage and should be avoided in production code. It is better to use a CREATE TABLE statement, where you can specify constraints and datatypes in advance, making it less likely that inconsistencies will sneak into the data.

  • Article

    Improving legacy code using SQL Prompt code analysis

    SQL Prompt code analysis, introduced in v9.2, provides a quick and easy way to assess the overall health and quality of your SQL scripts. It will help the team prevent technical debt from entering the code base, to remove it from existing code during code reviews, and to ensure that the released code conforms to the team's defined coding standards.

  • Article

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

    If you want to use TOP with an expression or subquery, or in INSERT, UPDATE, MERGE, and DELETE statements, then use of brackets is required, so it's a good habit to adopt everywhere.

  • Forums

    SQL Prompt Forum

    Write, format, and refactor SQL effortlessly