Product articles SQL Prompt SQL Code Snippets
SQL Snippets Galore, Added to SQL…

SQL Snippets Galore, Added to SQL Prompt

Imagine having at your fingertips all the metadata queries you need to explore your SQL Server databases and then, a few clicks later, all your diagnostic queries to troubleshoot their performance. Phil Factor demonstrates how it can be done, by storing each set of snippets in a standard JSON collection and using PowerShell to convert them to SQL Prompt snippet files.

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.

Extending the built-in code snippets

When you get SQL Prompt, you’ll eventually outgrow the built-in SQL code snippets, and you’ll want to change them as well as add your own. At first, you’ll paste-in your favorite routines, one at a time, but soon this will seem laborious.

In this article, I’ll show you how you can, instead, create your snippets as XML files, almost to the same standard format as Visual Studio Snippets, and then either add them to SQL Prompt’s default snippet directory, or to a custom directory, according to the task at hand. You can take existing resources, such as SSMS templates, or lists of good SQL queries or fragments that you use repeatedly, save them as JSON collections, which you then chop them up into snippet files using a bit of PowerShell.

You can maintain directories of special snippets, each for a particular purpose, such as a directory for diagnostic queries, another for metadata queries, one full of snippets for maintaining Service Broker, and so on. You can switch from one directory to another simply by altering the location of the snippet folder within Prompt’s Snippet Manager. For example, here is Prompt’s Snippet Manager after I’ve switched SQL Prompt’s snippet directory to one containing all of Glenn Berry’s diagnostic queries:

I keep up to three hundred snippets in a folder, but it takes a few seconds to read them in and a few seconds to index them.

Why not use just Prompt’s Snippet Manager for adding snippets?

The best SQL Code collections come as text files, often containing over a hundred scripts. Glenn Berry, for example, publishes the standard collection of diagnostic SQL DMV queries. They are the state of the art in diagnosing problems with SQL Servers and databases and Glenn is constantly improving and expanding his collection, with every new release of SQL Server. You want those as snippets but there are 85 of them and the SQL Prompt interface soon gets impractical as the sole way of importing them. Although you can import all your SQL Prompt settings, there is no way of importing just a set of snippets into an existing snippet collection.

In the case of Glenn’s diagnostic queries, the task of keeping your snippets updated can, unless you continue to read this article, get even more complicated, because you really need to use the right diagnostics for the version of SQL Server you’re investigating.

Let’s take another possibility. I use AceText, which is a programmer’s clipboard that allows you to make collections of clips. Like many other developers, I hoard good stuff from wherever I find it. In my case, I simply copy it, which creates it as a “clip” in AceText. When I have a quiet moment, all the items in the clipboard history that I’ve grabbed are moved to a snippet collection.

The Acetext collections are in a simple XML format so I can quickly convert them to the neutral JSON format and then to a directory of snippet files in the SQL Prompt snippet format. I can, if I want, do the reverse, and create an AceText clip collection from a collection of SQL scripts or even SSMS templates.

A word of caution before we dive in though: any way of creating a snippet other than by using the snippet manager isn’t currently supported by the SQL Prompt team. If you go to them for help with your snippet-file creation routine, you’ll get a cold-shoulder. Even I’m not going to help you out, beyond what is in this article.

OK, so what is a code snippet?

A code snippet is stored and distributed in a standard XML file format for Visual Studio. This format was defined in 2005. Code Snippets contain code that can be inserted into an application using a code editor. Programmers can speed up development time by using code snippets instead of wasting time typing repetitive code or ‘googling’ for samples. The Code Snippet XML schema allows you to create your own code snippets and add them to your code editor. Many products use this format though the snippet formats in Linux tend to be different.

Snippets consist of two sections:

      • a Header section – tells you the snippet title, and the Visual Studio (VS) shortcut used to invoke the snippet, which must be unique.
      • a Snippet section: the code is placed in the snippet section, along with the declarations for the user placeholders.

This is the basic XML format (see here):

The SQL Prompt snippet format is a slightly different format to the VS Standard and it therefore has a different filetype.

JSON Snippet Central: managing snippets from multiple sources

The original XML format was designed to hold a collection of snippets. However, I prefer working with JSON because, for me, it is far less hassle. To manage larger numbers of snippets from various sources or for various purposes, I like to store each collection of SQL snippets as a JSON collection. Each source requires a PowerShell script to convert it into a JSON collection.

For example, I have a PowerShell script that takes as its input the directory containing all the SSMS template files (arranged into subdirectories). It converts each file into a JSON snippet. I have another that takes as its input the SQL file containing all of Glenn Berry’s diagnostic queries. It shreds the file, turning each query into a JSON snippet. I’ll demo both scripts shortly, the result in each case being a JSON file consisting of an array of JSON documents, one per snippet.

This beauty of this technique is that the JSON snippet format is a standard format, independent of the type of snippet, making it easy to convert between different types of snippet. We just need a single PowerShell script to convert any JSON file into a collection of SQL Prompt snippet files.

At times of course, you’ll need to edit the JSON snippet collections, or search for a particular snippet. For this, I store them all in a MongoDB database and use Studio 3T, the best JSON editor I’ve yet found, to store, search, sort and edit any of my JSON collections (I’ll demo this later in the article).

You can also save the JSON collections safely in source control. A team can then work on adding or altering snippets, saving them in the JSON format and updating the snippet files, when convenient. This means that you can maintain as many collections of SQL Prompt snippets as you need and exchange them with the rest of your team. You can update them from whatever source you use in a few seconds.

However, be aware of a few incompatibilities. SQL Prompt has several built-in placeholders, mainly to allow it to create ‘surrounds with’ snippets, which aren’t portable. SSMS Templates don’t have any built-in placeholders, and SSMS snippets have only two undocumented ones that are different to SQL Prompt’s. However, the angle-bracket template convention that SSMS uses for filling in the values for user-placeholders in templates can be used by any snippet. SSMS uses the angle-bracket macros to create a form, after the snippet or template is inserted and when it detects the Ctrl+Shift+M keystrokes to ‘Specify values for template parameters’.

Converting all SSMS Templates into a JSON snippet collection

Here is the PowerShell code to convert all SSMS templates into a JSON collection. You will, of course, need to change the location of $WorkDirectory, and possibly the name of $OutputFile, to whatever you wish. You might also need to change the location of $SnippetDirectory as it changes from version to version.

Conventionally, each SQL Prompt snippet is identified by a meaningful, 3-4 letter keyboard shortcut and most of the complication in this script is in getting a reasonable set of shortcuts. Neither SSMS templates nor SSMS snippets have shortcuts so this script creates them by taking the first character of each word in the <title> element. There is a section of the code that assigns two-letter acronyms in cases where there are server objects or commands that have the same first letter (so the Create Credential template gets a shortcut of CCr, for example).

Each shortcut must be unique, and in some cases the initial character of the template category, such as “C” for “Change Data Capture” (which is the subdirectory name), is added to the start of the shortcut, to avoid duplication.

This done, we create the JSON snippet, constructing a header from various bits of information and inserting the template code into the snippet body, and add it to the JSON collection. You’ll see that I include the source of the snippet in the header, to allow you to mix sources in a single collection, and in case a particular source requires special handling.

Converting one of Glen Berry’s files to a snippets collection

Here’s the PowerShell script to convert Glenn Berry SQL Server Diagnostic Information Queries file into a JSON snippet collection file. In this case, I haven’t attempted to generate meaningful shortcut for the snippet for each these queries, because DBAs seem to prefer to refer to them by their number. It is easy to do, though as you’ll see shortly, because the titles are preserved in the JSON Snippet collection file.

Converting a JSON snippet collection to a directory of SQL Prompt Snippet files

Once you have your JSON collection files, it is a reasonably simple matter to convert them into SQL Prompt Snippet Files. This requires a simple script. It is currently set up to import templates but doing other types, like Glenn’s scripts, is just a matter of changing names.

And here is an example of SQL Prompt with all the templates installed as snippets. They will, of course, work by generating the form for you to apply values for the template parameters without having to invoke the Ctrl+Shift+M keystrokes to do so.

Storing, searching, and editing the JSON collections using MongoDB and Studio3T

The natural place to store JSON is in a JSON-based database, and I use MongoDB. Any JSON file produced by my PowerShell scripts can be inserted straight into MongoDB, where you can sort, search, and edit it.

The Studio 3T editor that I generally use makes all this very simple. Just open the object browser, click on your MongoDB database, then on Collections, and add a new collection (called Templates, say). Then open one of your JSON snippets files (in a text editor), copy the contents and paste them into the “result” pane of the new collection. It will import the documents and the result will look like this (in JSON View):

You can use the icons at the top right of this screen to search the collection and to edit it.

You can, of course search through a MongoDB database for even the shyest snippet and extract whatever collections you want with a query. Just open the IntelliShell from the top menu (or right-click on your JSON collection and select Open IntelliShell) and then use the following code:

Sometimes, you just want to select your favorites from a large collection of mixed snippets. If so, you’ll want to ensure that all the shortcut names in the collection are unique. If they’re all from the same source, you can be sure that they are unique but if you are picking your favorites from several different sources then all bets are off. You’ll normally start with a filter that selects just the snippets you want to use.

You can export results as JSON, or use MongoExport to do so. If you prefer to use SQL to administer your collection you can also import your entire database of snippets directly into SQL Server with point n’ click, via Studio3T!

You can always check a JSON snippet collection file from a particular source for duplicate shortcuts this way, but the MongoDB search will find duplicates from all your sources

Conclusions

The built-in SQL Prompt Snippets are good for doing very simple SQL Operations, but code snippets are a powerful tool, and any SQL DBA or developer will eventually want to build up their collection.

As your snippet collection grows, the question of how you are going to manage them all will loom larger. In this article I’ve tried to offer a sensible solution by showing how you can create simple snippet collection files that can be used to then create SQL Prompt snippet files. I’ve shown examples for the SSMS built-in snippets and Glenn Berry’s diagnostic queries, but you can, of course, create other types of snippets, such as snippets for other IDEs.

What about having a standard header or creating an extended properties documentation to go with your snippet collections? All this can be done, but this is outside the scope of this article. However, by having a standard non-specific storage format such as the JSON Snippet Collection files, you aren’t going to be overwhelmed with scripts, and you’ll make source control easier.

Tools in this post

SQL Prompt

Write, format, and refactor SQL effortlessly

Find out more