25 September 2017
25 September 2017

Templates and Snippets in SSMS and SQL Prompt

Guest post

This is a guest post from Phil Factor.  
Phil Factor (real name withheld to protect the guilty), aka Database Mole, has 30 years of experience with database-intensive applications.

Despite having once been shouted at by a furious Bill Gates at an exhibition in the early 1980s, he has remained resolutely anonymous throughout his career.

He is a regular contributor to Simple Talk and SQLServerCentral.

In SSMS, we have access to templates that provide building blocks for creating various types of query, common routines to create various schema objects, or to do common DBA tasks such as running backups. We can also use SSMS T-SQL code snippets to define the basic structure for a block of code or a T-SQL statement that creates the common types of database objects.

Both templates and snippets save coding time, and introduce standards and consistency to the way you build certain modules, for example in terms of structure of any headers, inclusion of guard clauses, and so on. However, the native SSMS versions of these features have some serious limitations, which I’ll describe, before going on to demonstrate how SQL Prompt snippets add a versatility and flexibility that I, and I think most programmers, appreciate.

Why ask for more than what’s in SSMS?

Snippets and templates in SSMS are a popular feature. Templates are reasonably easy to use from the template window either by drag-and-drop, or by double clicking to open the template in a new query window. One quick keystroke later (Ctrl-Shift-M) and you have a form to fill in with the important details. Fill in the form and all the embedded macros have values in them. This is useful, but there are some obvious things missing:

  • You can’t turn a selected query into a view, function, procedure, trigger or spExecuteSQL batch. You need to be able to select your query and pick the template and have it put the selected text embedded within in the body of the function.
  • There is no way to put dynamic macro values in automatically for headers or inline comments. For some of these, you need values such as the date and logged-in developer, or database-wide values such as the database name.
  • The template window needs to be visible when you need to use it. With small screens, you tend to want to hide templates away to give yourself more real estate. It was this last factor that seems to have led to the SSMS snippets, even though their functionality overlaps with templates. Just right-click and you’ve got a menu of SQL objects that you can inject as build scripts into your query window at the insertion point. It is quite a lot easier.

SQL Prompt offers a type of snippet that aims to do away with the frustrating restrictions of SSMS. Essentially, with SQL Prompt, you now have three different ways of accessing snippets in SSMS, two native and one provided by SQL Prompt. Each has its advantages and disadvantages. There is some overlap in functionality.

SSMS Template SSMS Snippet SQL Prompt Snippet
Can you insert currently-selected text? ✅(only into BEGIN…END, IF and WHILE blocks.)
Can you include database-specific macros? ($DATE$, $DBNAME$, $MACHINE$, $SERVER$ and $USER$)
Can you create an entry form for macro values? ✅(if you hit Ctrl-Shift-M)
Is it always there when you need it?
Can a team share its templates/snippets?
Do you get ‘replacement points’? (you are prompted with a description of the syntax element you can specify at that point) ✅(several copies of the same replacement point are allowed)
Can you create your own snippet? ✅(but only by editing raw XML)

How SSMS Templates work

An SSMS template is a ‘boilerplate’ for performing a common database management task. We can, for example, use a template to create, modify or drop various schema objects, alter a database audit specification, or add a linked server. In SSMS, just use Ctrl-Alt-T to bring up the Template Browser and locate the one you need. If you open a template, by double-clicking, you create a separate copy of the code in a new query window, and any changes you make won’t affect the original template. To place a template into existing code, you drag and drop it from the template window into the existing query pane. If you want to edit the existing template to better suit your requirements, simply right-click it and choose Edit. To create a new one, choose New | Template.

Listing 1 shows a typical SSMS template for creating an inline function. I’ve edited he built-in template to include a guard clause, which will delete any existing version of the table function, and to use my preferred header structure, which can easily be read by PowerShell.

Listing 1

You will notice the parameter placeholders contained in angle-brackets, with three comma-delimited parameters of the form <Parameter, Type, Value>. If you want to apply the same macro several times, as in the name of the function above, you need to repeat the same definition, identically.

Each time you need to create an inline function, you just open the template, and hit Ctrl-Shift-M, to bring up the form for specifying the template parameter values, such as the name of the object, the names and types of any parameters that the object accepts or returns, and so on.

Figure 2

Now you can just fill in the form with the values you want.

Figure 3

When you hit OK, SSMS will run the macros to replace the whole strong between the angle brackets with the values you supplied. The result looks as shown in listing 2.

Listing 2

Of course, there is a lot still to do, and the body of the function needs to be stitched in. We can always use an SSMS snippet instead.

How SSMS Snippets work

A snippet is like a lightweight template and can be used in a similar fashion, with the advantage that it is added at the current insertion point. A snippet is any pre-defined block of code, which you can quickly insert into a query window, at the cursor position, just by right-clicking and locating the required snippet.

Right-click in the query window choose Insert Snippet… from the context menu and then select the Function folder followed by the type of function you wish to create.

Figure 4

This inserts the snippet, with the various replacement points, within the code highlighted. Each replacement point corresponds to a declared literal within the snippet’s XML file.

Figure 5

Each replacement point has an associated ToolTip with text that suggests the syntax relevant to that point. You can use the Tab key to move between each replacement point.

You can manage all code snippets using the Code Snippets Manager, which you can reach via Tools | Code Snippets Manager…, or by the keyboard shortcut, CTRL+K followed by CTRL+B. From here, you can view the names and descriptions of snippets, remove them individually and import all code snippets.

Figure 6

The Location entry shows where the snippet’s XML file is saved. If you want to modify an existing snippet, say to add a guard clause and header, you’ll need to edit raw XML. Likewise, if you want to import a snippet, you’ll likely have to edit the XML to get it into the right format.

By editing the XML, you can create a snippet for a function that embeds your currently selected text within the body of the function. It isn’t easy, and would take too long to explain in this article.

This brings us to SQL Prompt snippets.

How SQL Prompt Snippets Work

I want a better ‘template’ than is possible with either the SSMS template or the SSMS Snippet. SQL Prompt Snippets make it easier to create snippets, either from scratch, or by creating a snippet from selected text in a query window, via the right-click context menu. It is simple to include multiple replacement points for the same parameter. I can edit a SQL Prompt snippet without having to delve into XML.

SQL Prompt comes with many pre-defined snippets, as well as allowing you to create your own. To open an existing snippet within a query window, simply start typing the snippet’s name, which by convention will be the initial letters of its description, so citf for “create inline table function”.

Figure 7

However, I want my citf snippet, shown in Listing 3, to contain my usual headers and guard clauses. They are a chore to do by hand, and I dislike chores.

Listing 3

Notice that that the function name needs to be inserted three times, at least. If you want to give examples of usage in the header, then you will need even more.

In the header, I’ve used some normal SSMS template parameters, within angle brackets, as well as some built-in Prompt placeholder macros to auto-fill the connected user and database, and the current date. I’ve even included some example executions.

At the point where I need to define the body of the function, I’ve used the $SELECTEDTEXT$ placeholder, which means that any selected text in an SSMS query window can be inserted into the body of the function

To replace SQL Prompt’s built-in citf snippet with my own, I just need to edit it in SQL Prompt Snippet Manger (SQL Prompt | Snippet manager).

Figure 8

Hit Save, then OK. Now, In SSMS, we write a query that will find all the addresses in a city, for the AdventureWorks database.

Listing 4

With the text of Listing 4 highlighted in the query window, click on the red speech bubble, called the Prompt action list, and select the citf snippet.

Figure 9

Immediately, we’re confronted with the SSMS form for specifying the template parameter values.

Figure 10

I fill it in, bang the OK button, and can then fill in the function name. I just need to highlight and fill in the first one and SQL Prompt replaces all occurrences.

Figure 11

Now I tidy it up a little bit, to give it the right parameters, and execute it.

Listing 5

We can now highlight the example in the header and hit ‘execute’ in SSMS to check it

Figure 12

Job Done!


I hope this has demonstrated that the SQL Prompt Snippets have considerable power to save you a lot of time. Some DBAs store all their most common DBA checks in them, and a developer like me uses them to try to aim for a better quality of code in the same amount of time. If you’re told that you don’t need SQL Prompt snippets because what is in SSMS is good enough, I’d just give them a sad, pitying look.

One curiosity about the SQL Prompt snippets for me is that so few of the examples really stretch the capabilities of the system and I found it hard to find worked examples of everything I wanted to do. It is worth experimenting.

Tools in this post

SQL Prompt

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

Find out more

Guest post

This is a guest post from Phil Factor.  
Phil Factor (real name withheld to protect the guilty), aka Database Mole, has 30 years of experience with database-intensive applications.

Despite having once been shouted at by a furious Bill Gates at an exhibition in the early 1980s, he has remained resolutely anonymous throughout his career.

He is a regular contributor to Simple Talk and SQLServerCentral.

Share this post.

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

Related posts

Also in Hub

Finding code smells using SQL Prompt: TOP without ORDER BY in a SELECT statement

Using TOP in a SELECT statement without a subsequent ORDER BY clause is legal in SQL Server, but meaningless because asking for the TOP 10 rows implies that the data is guaranteed to be in a certain o...

Also in Product learning

Choosing Between Table Variables and Temporary Tables

People can, and do, argue a great deal about the relative merits of table variables and temporary tables. Sometimes, as when writing functions, you have no choice; but when you do you’ll find that b...

Also in SQL Prompt

Avoid use of the MONEY and SMALLMONEY datatypes

The MONEY data type confuses the storage of data values with their display, though its name clearly suggests the sort of data it holds. It is proprietary to SQL Server and allows you to specify moneta...

Also about SQL Prompt

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