Tag Archive for Script

SQL Advent Calendar 2013-Day 1- Placeholders in SQL Prompt Snippets

AdventCalendar01Today my daughter Natalie and I started our first LEGO Advent Calendar – Star Wars edition. While this particular calendar is not Christian centric, it’s a lot of fun for us. For those that aren’t familiar with an Advent Calendar, it’s a calendar that counts down the days until Christmas. Each day a small gift or message is given.

My husband grew up with a handmade Advent Calendar. He told me when we were engaged that he wanted one for his family to use. Since we got married 6 weeks after I graduated from college and I had no money, I made one for my wedding present to him. As I was hanging it up today, I thought I would make one for you, my readers. Over the next 25 days, I’ll share my scripts with you from two of my favorite products as well as tips on how to create your own.

SQL Prompt By Red Gate

One of my favorite tool companies is Red Gate out of the UK. I not only love their tools, but I love their service. They care not only about their products, but what we think of their products and how we use their products. Over the last year I have had the honor to be part of their FoRG group (Friends of Red Gate). As a FoRG member, I get to give my suggestions directly to the project managers and others seeking insight from the FoRG group. Don’t think you can’t give your two cents too. Red Gate loves to hear from everyone. In fact, at PASS Summit this year they dedicated their whole back wall to receiving sticky notes for ideas on how to improve all their products. By the end of the conference, it was quite full.

SQL Prompt was the first of their products that I used. I use it just about every time I’m in SSMS, and have been very fortunate to be able to use it at my new job. (It’s very frustrating when I can’t use it.)

SQL Prompt does a couple of things for you. The most obvious thing that it does, is help you write SQL faster. Yes, SQL 2012 has intellisence, but SQL Prompt has a better version. The second feature that I love is the code standardization options. There are several settings and shortcut keys that you can use to standardize your code. The third feature that I love and will be featuring in my SQL Advent Calendar, are templatized scripts (called snippets). The snippets are accessed by keystrokes that you specify. A library of snippets comes with SQL Prompt, but you can also add your own.

ER/Studio Data Architect by Embarcadero

Embarcadero has a very wide range of products, and one of those is on my favorite tool list. I’m sure you can guess what it is based on my last couple of posts here and here. It’s Embarcadero’s ER/Studio Data Architect.

ER/Studio Data Architect is a database modeling tool. It’s a great product to use as the master copy of a database schema. It also has the ability to create scripts for different versions of databases. This came in real handy when I needed to script a database model for both SQL 2000 and SQL 2012.

One of the features that I really like ER/Studio Data Architect, is the ability to create and use macros inside the product to update the database model. All you need to know is VB script and the Object Model.

So, without further ado…..

Day 1 – Placeholders in SQL Prompt snippets

You may ask why I like the SQL Prompt snippets since SQL 2012 has a template library that is very similar. There are currently 8 reasons why I like them. ($DATE$, $TIME$, $USER$, $PASTE$, $MACHINE$, $CURSOR$, $SERVER$, and $DBNAME$) These are the 8 placeholders that can be used in conjunction with the snippets. As each name suggests, they represent a piece of information that is unique. $USER$ will be replaced by the name of the User logged into SSMS and $DBNAME$ will be replaced by the name of the currently active database. $PASTE$ will grab what is in your copy buffer and $CURSOR$ will move the cursor to the that location in your snippet. (An important thing to remember is that the placeholders are case sensitive.)

Note: There are 8 placeholders with version 6.1, which is the current version. Other versions may have a different set with different functionality. Visit the documentation for the version you have to see the current list.

To create an SSMS placeholder you use the syntax below.

  • Where X is the name of the placeholder and should be very descriptive. It will be viewed in a dialog box with all the other placeholders
  • Y is the data type of the placeholder. Most of the time I use “string”
  • Z is the default value for the placeholder and is optional, but you still need to have the third parameter position available. This is where you can really leverage the SQL Prompt Placeholders.

In the example below I created three SSMS placeholders and I use three different SQL Prompt placeholders. Two of the SSMS placeholders are identical, so they will appear as one placeholder to the user. Notice that $DBNAME$ is used inside the SSMS placeholder. This will allow the current database name to be the default for that placeholder.

USE <Database Name,string,$DBNAME$>

--Created by: $USER$ on $DATE$

     <Database Name,string,$DBNAME$>.sys.objects AS o
     name LIKE '<Search Criteria,string,>%'

To use the code above, copy it into a new snippet in SQL Prompt.

  1. Open up the Snippet Manager.
  2. Click the New button.
  3. Name the snippet TNS.
  4. Set the description Table Name Search.
  5. Save the script.

In a new query window type TNS+. Notice that your SQL credentials were put in place of $USER$. Today’s date was put in place of $DATE$ and the current database was put in place of $DBNAME$.


Now let’s leverage the SSMS placeholders by typing ++M. You can now accept the default values, or you can change them. After you click OK, you can run the script.

That’s a wrap

I hope you enjoyed the first day of 2013 SQL Advent Calendar.

Note: Since I live near the end of the GMT day, the calendar will be done based on the PST zone.

Next Post In Series:

SQL Advent Calendar 2013 – Day 2 – Macro to Remove Common Fields in ER Studio Data Architect


%d bloggers like this: