• SQL Prompt
• SQL Code Snippets

Using SQL Prompt Code Snippets for Repetitive Tasks

There was a time when I didn’t think I needed IntelliSense. After all, I can write T-SQL in my sleep, know most of the common syntax by heart, and the more complex or arcane bits are only a quick web search away. However, SQL Prompt changed my mind. I started using its code completion suggestions to suggest join criteria, then to help fill in GROUP BY clauses, and so my use of the tool increased, until I calculated that it now saves me tens of minutes, at least, every day.

Until a few weeks ago, I didn’t think I needed SQL Prompt code snippets either. However, when I finally found time to learn about how they worked, I realized that they could be a lot more than just helpers to provide fragments of SQL text for the basic commands. A few simple, custom snippets could shave a lot of time off of many of my repetitive tasks, such as:

• Inserting repeated text, like comment blocks or separators
• Inserting commonly used blocks of code that are nearly the same every time
• Running utility code (even large batches)

Having figured this out, I find myself creating and using snippets for many of the repetitive tasks I do on a regular basis.

What are code snippets?

If you don’ t have SQL Prompt installed, you can get the addictive free trial here. As it states in the documentation, a snippet is:

“Any pre-defined block of code, which you can insert into a query pane at the current cursor position, or around currently selected SQL text“.

The word snippet implies to me, “small”. Open the Snippet Manager from the SQL Prompt menu, and you’ll see that the built-in snippets bears out this expectation:

Each of the predefined snippets are small code fragments, such as for creating or modifying common object types, which you can invoke with a few keystrokes rather than having to type in the whole fragment. So, for example, typing ct invokes the “Create table” snippet, and inserts the bare bones of a CREATE TABLE statement, and the “j” snippet saves you the onerous task of typing JOIN. I don’t use these built-in snippets much. Perhaps they would save me a few keystrokes here and there, but it would probably take me longer to remember the initialism of the snippet I needed.

However, once I started to understand how the Prompt snippets worked, I realized that I could build my own custom snippets, and that they didn’t necessarily need to be small and simple (although some of them are). Doing so could remove a lot repetition, particularly when performing frequent tasks such as adding comment headers, creating tables, or executing commonly used metadata queries. Their support for placeholders (parameters, more or less) means I can very quickly substitute in the correct names of various objects, or the values for query parameters, as required.

Note that SSMS has its own snippet manager that also has syntax examples for building common object types, as well as support for templates, and it’s useful to understand how and why SQL Prompt’s snippets are different.

One of the big differentiators for me is usability; SSMS snippets are invoked via a right click in the query editor, but SQL Prompt’s snippets work just like any other IntelliSense helper, making it a good deal more natural to work with them.

Simple snippets for inserting repeated text

Even very simple snippets can remove a surprising amount of tedium from small, but frequent and repetitive, tasks. I rarely review a piece of code without feeling the urge to make small improvements, such as by adding a header comment to explain the intent of a routine, and a set of dashes to help break up some large block of code into logical sections.

Habitually, I just type out the comment header the first time and then use copy and paste. Instead, however, I’ve now created a Prompt snippet from the following code:

The $ characters denote a placeholder, a replacement point for a parameter value. $CommentHere$ is a custom placeholder, where I can just enter the text for my comment header, and $CURSOR$ is one of SQL Prompt’s built-in placeholders, which in this case simply specifies where to place the cursor once I’ve invoked the snippet and filled in the $CommentHere$ placeholder. Other built-in placeholders will perform specific actions, such as inserting the current date ($DATE$) or the name of the connected database ($DBNAME$).

You can create snippets from scratch by opening the Snippet Manager that I showed earlier, but to create a snippet directly from this code, simply highlight it in an SSMS query pane and then right-click and select Create Snippet, from the right-click context menu (or from the Action menu, which is the dropdown icon you can see at the top left):

This captures the code into a snippet, automatically detecting any placeholders, and assigns it a default name, which was cc in my case. I dislike these short initialisms and immediately changed it to a longer but more memorable name, and added a description:

As you can see, the Create New Snippet dialog has a section that keeps track of these custom placeholders, and you can provide a default value for each one (for a LIKE expression placeholder, you might use a default value of '%', for example).

Click Save, then OK on the Options dialog, and go to SSMS. Type the word commentsection, and as you type, you will see the following:

Complete typing commentsection, or choose it in the menu, and press enter to invoke the snippet and it will inject the code fragment into the query pane, at your cursor position. Now, the $CommentHere$ parameter will be selected, and you can start typing. If you have multiple parameters in the code with the same name, they will all receive the same text as you start typing in one of them.

Once you hit Enter, your comment text is saved, and the cursor moves to the position defined by the $CURSOR$ placeholder. If you need to type in a multi-line comment header, just use Shift-Enter, rather than Enter.

You can easily extend this example to create a custom comment header for the entire team. For example, you could standardize the file header using a template such as:

Name: teamfileheader
Description: LD-Use this header at the beginning of each SQL file
Snippet Code:

When this snippet is invoked, the author will be filled in with the username of the person logged in, the date in local format (no time), and you will be able to describe the purpose of the script.

For further possible inspiration, check out Phil Factor’s interesting demonstration of How to use the SQL Prompt snippet placeholders for selecting and copying text.

Snippets for creating objects

Whenever I create database objects, for whatever reason, there is often some ‘repetitive element’ to the task. For example, when I create a table, it will almost always have certain common characteristics, or I often need the same set of objects every time I perform a task, such as to fetch all the rows recently-added to a table, for reporting.

Once I started thinking about the kinds of tasks I perform frequently, the ideas for snippets started to pile up!

A more useful Create Table snippet

Each new table I create will, of course, be different in name and overall structure, but every table we create includes the following common requirements:

• A surrogate primary key
• A column to indicate when each row was created, and one for when it was modified – each of these columns will have DEFAULT constraints, which include the name of the table and the column
• PAGE compression will usually be enabled for the table

In a more complete solution, I might include code to see if the table already exists, drop an existing version of the table, add extended properties, and so on. To create a standard template for these common table features, and to remove the repetitive element from the task, I created my custom createtable snippet, as follows:

Name: createtable
Description: LD-Base table create with our standard items pre-filled
Snippet Code:

The placeholders are $SchemaName$ and $TableName$, the latter being repeated several times. Invoke the snippet, type in the schema name, and then start typing the table name, in this case I called it NewTable:

When you edit one instance of the placeholder, all of them change! Finish typing the name and press enter:

I may not use the surrogate key as is, but I will certainly have a primary key on the table, and it will be named according to the standard established in this snippet. In most tables I create, I will also need the RowCreatedTime and RowLastModifiedTime columns. I have manually created default constraints with that exact same pattern hundreds, if not thousands of times in the past five years.

Naturally, creating much of the rest of the table is not so formulaic as to be made into a snippet, but what this snippet provides is a generally usable framework of the common repetitive parts. I have also created column snippets, which will define the necessary DEFAULT, CHECK, or FOREIGN KEY constraints, and their naming convention (typically including the table name and perhaps the column name).

A snippet for monitoring rows created in a table

The next example is from a project where I had to prepare a daily report on new activity that occurred on a set of tables. For each table, this requires a couple of stored procedures to fetch and save the rows added to the base table, after a certain date, and a “UsedRows” table in which to store the results, for reporting.

This is another perfect task for a snippet, since we’re basically using the same set of objects to fetch the data for each table, though the name and datatype of the surrogate key may change.

Here’s the code for the snippet:

Note that the snippets are smart enough to handle the use of the $ character in object names and strings, which is a feature of my personal naming style. Parameter names must not have spaces in the characters of their names, but you would have issues if your naming standard required multiple $ characters without a space character.

Tools to execute utility code

One of the first things that came to mind when I started looking into how to use snippets was to use them for code that I frequently executed, from files saved in source control, often to answer questions about an instance’s state or metadata. I came up with two ways to do this, and I have employed both with success.

1. Use SQLCMD’s :r command, within a snippet, to access and execute a specified .sql file from a list of files stored in source control
2. Capture the SQL you need into a snippet so you can grab the code, adapt it as needed, and execute it

Which method to use for a certain script depends on how likely you are to need to make changes to the code, each time you need to run it.

A SQLCMD ‘clipboard’ snippet

So, let’s say you have a whole set of useful metadata queries, all of which can be run “as is”, and each one is stored in a separate file in a change management system (GitHub, Dropbox, and so on), and shared with the team. As a very simple example, one of them might be a file called CheckLoginSystemCount.sql that reported the login count for a SQL Server.

Instead of having to hunt around for each file, when I need it, I’ve created a snippet that is really just a list of SQLCMD :r commands, each one pointing to a file containing one of these metadata queries.

Name: querylist
Description: LD-Link to commonly used metadata queries
Snippet Code:

Now, when you invoke the snippet, in an SSMS query pane set to SQLCMD mode, you get the list of the files. Simply highlight the :R line that points to the file you want to run, and it will execute what is in the file. The EXIT lines make sure that you don’t accidentally execute every script.

A snippet for investigating lock activity

I have several reasonably complex DMV queries that I run quite frequently to investigate current activity on my database instances. As an example, I have a query to look at locks currently held in a database. It is taken from the book on DMVs that Tim Ford and I wrote over 10 years ago (though it has some great queries that are still relevant).

Again, I used to store this in a file in source control and ferret it out as I needed it. 90% of the time, I could run it “as is” but for the other 10% I’d need to add filters, select different columns, and so on. Annoyingly, that when I went to close the window, I’d invariably save my changes over the original file (the one that works for 90% of my use cases!).

I decided it was easier to create a snippet that contains the query, perhaps using the snippet parameters to make it easy to change the values for certain parameters, before running it. In my example, I have made placeholders for the type of resource, and the SPID, both defaulted in the snippet to return all values.

Name: locklist
Description: LD-Used to fetch the locks for connections in the current database
Snippet Code:

Now, I can just invoke the locklist snippet, execute the query, supply the filters or just let them use the defaults. If I save the query, I am not overwriting the template, and there will be no question, when committing code, if this change was important or not.

Note that it is still valuable to have your complex queries like this committed to source control. For all but the simplest snippets, the Snippet Manager should not be your only copy of the underlying queries.

Summary

Snippets are one of those things that generally have a bad name with hardcore programmers, who mainly regard them as too trivial to be worth their time. It was only after I considered how to use them to automate some of my repetitive tasks that I realized they were more valuable than I initially expected.

I’ve demonstrated several simple, yet time saving snippets to help make coding easier by eliminating extra keystrokes, or creating objects, or simply allowing easy reuse of code that is stored in files in source control.

Tools in this post

SQL Prompt

Write, format, and refactor SQL effortlessly

Find out more