• SQL Prompt
• SQL Code Snippets

Creating and Maintaining a SQL Code Snippet Library

As a database administrator or developer, you need a different set of SQL code snippets, or queries, for each task. For example, you could be coding something that currently runs too slowly, and you need to load in your performance test-harness snippet, to pinpoint the cause. Other times, you just need a query to tell to know roughly how many rows are in each base table. It is too much for ordinary mortals to remember.

Database people tend to be hoarders of this sort of SQL code. I use hundreds of scripts and snippets, curated over many years. Some I use all the time, most only occasionally, but when I need them, I need them fast, without the distraction of having to code them afresh.

After facing the frustration of losing useful SQL snippets, or hunting around endlessly to remember where you saved the one you need, the joy of a central repository, whether it’s a Git repository, JSON Snippet Collection or just a file directory, becomes apparent. My previous article introduced the idea of having sets of snippets for various purposes, each stored as a JSON Snippet Collection, which you could then chop up, using PowerShell, into individual snippets and use in SQL Prompt. Here, I’ll extend that idea, showing how to convert other types of snippet, such as SSMS code snippets, into JSON Snippet Collections. I’ll show how to use a JSON database plus editor to store, search and edit them, and finally some PowerShell scripts to write them out in the various required formats such as for use in a programmer’s clipboard, or to store them in a directory structure.

This provides a system where you can keep the snippets you use all the time close at hand, and then have a repository from which you can quickly grab the snippets you need more occasionally.

Collecting and using SQL code snippets

SQL Prompt comes with a set of built-in SQL code snippets, but these were designed just to give you some ideas, rather than to become the definitive list. I estimate that a comprehensive set of useful example code would require well over a thousand snippets. After all, there are nearly four hundred SSMS templates! In addition, are some good public collections. Others are scattered in articles and books. I often stumble over such code snippets when busy doing something else, but I screech to a halt, grab them quickly, save them to my programmer’s clipboard, and resume what I’m doing.

Every database developer I know does this a different way. I use AceText the whole time, so I just copy snippets onto the keyboard and put them into an Acetext collection as soon as I get a spare moment. I also use PowerShell to grab public code collections such as Glenn Berry’s diagnostic queries and use PowerShell scripts to slice them up into individual snippets.

Of course, after a certain point, finding snippets amongst the several hundred can be a problem. Also, categorizing them can be tricky. After all, diagnostic queries can be specific to a version of SQL Server or even the platform. They are certainly different types of snippet for all the types of diagnosis you need.

Another problem is in deciding how you use them. In SSMS, for example, do you use an SSMS Template, a SQL Prompt Snippet or maybe an SSMS snippet? Do you, instead, rely on Tab History to find them. Do you just put them into your Clipboard Helper such as AceText? Maybe you want to use more than one method depending on the task in hand.

Saving code snippets to a central JSON Snippet Collection

If you have several choices of where you store your snippets, you can save a lot of conversion by having a central repository. You then just need a way to produce from the repository a collection of snippets in a form appropriate for each different way of using them, such as within SQL Prompt, SSMS templates, Visual Studio, or a clipboard tool.

A JSON Snippet Collection isn’t the only way of doing it, of course. You can store snippets as individual files in a directory, with carefully chosen subdirectories, in much the same was as SSMS uses directories to categorize snippets and templates. This works well, but the means of search is a bit limited, and it can be easy to make mistakes in the JSON schema you choose.

Whatever the purpose of the snippet, you really need a single generic schema to store each snippet. After all, the original VS snippets had a defined schema. The schema that you use for each generic snippet must accommodate every type of snippet you envisage using. The classic Visual Studio snippet is probably the most complicated, so I stick closely to that. After all, it is a publicly defined standard. The category can represent a path to allow a directory-based tree categorization.

Turning XML snippets into JSON, via PowerShell Hashtables

We’ll need this routine for converting XML snippets, such as the old-style SQL Prompt snippets (.sqlpromptsnippet), or SSMS code snippets, to JSON. It is easy to transform either of the XML formats into a PowerShell Hashtable and from there to JSON, or YAML.

Normally, when working with PowerShell, we are more concerned with getting specific information from these XML snippets, such as the title or a particular string. Here, though, we’ll want to do an almost direct transformation of the XML snippet format into our JSON Snippet Collections.

Here is the code for the ConvertFrom-Snippet function that will take an XML snippet and produce an ordered hashtable.

OK, and we can try it out on a SQL Prompt snippet. Here we turn it from the .sqlpromptsnippet format into a JSON document

And here we do the same with an SSMS Code Snippet

Save all SSMS code snippets to a JSON Snippet Collection

We can save all the existing SSMS snippets to a JSON Snippet Collection (SSMSCodeSnippetInfo.json), using our function to interpret XML snippets straight into hash tables. Most of this can be used intact for our standard format, except that we store the name of the directory where the file was found, to define the category.

Save all XML-format SQL Prompt snippets to a JSON Snippet Collection

Because the old XML SQL Prompt format was close to the Microsoft standard, we can use almost exactly the same routine for them, or for any other sources of snippet using that standard.

Storing, searching, and editing the JSON Snippet Collections

For me, 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. Your input routine can check for duplicates so you can have less fear of bulk imports or updates.

It is pointless having a central repository if you can’t edit the code in what is likely to be the primary source, to add code, remove duplicates, or correct parameters. 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 Snippet 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

Writing out JSON Snippet Collections

Once you have all your snippet collections in a central JSON repo, there are several different ways you might want to use them, depending on their purpose.

Write out a JSON Snippet Collection to an AceText collection

Here we write out an SSMS template collection (TemplateInfo.json) to an AceText collection so we can then paste templates into SSMS directly from our Clipboard.

If you don’t have the TemplateInfo.json file, use the code in my previous article to create it.

Writing a JSON Snippet collection into a directory-based repo

Some people are happier to have their snippet repository laid out as files rather than snippet JSON Snippet Collections, especially to maintain them. You can expand the JSON files into a directory structure, rearrange, delete and edit the individual files and then read them back into a JSON directory. It certainly makes them easier to edit. However, this might cause confusion if someone mistakenly believes that these are snippets for a particular application (such as SSMS or SQL Prompt), rather than just a form of archive, and tries installing them.

Here is how you can make a directory of snippets, with subdirectories for each different category of snippet. I’m using the SSMS template repository for this example, but it will write out any collection (just change the collection and the SnippetFileType, as required). It will create subdirectories for any category you have and even subcategories delimited by ‘\’.

Write out a JSON Snippet collection to SQL Prompt

I’ll show how to convert a JSON Snippet Collection into SQL Prompt snippet files, in either JSON or old-style XML format. As an example, I’ll use Glenn Berry’s diagnostics queries.

Glenn publishes the standard collection of diagnostic SQL DMV queries. The 85 (at time of writing) queries 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. If you’ve not yet saved Glenn Berry’s queries as a JSON collection, I provide the PowerShell code to create the DiagnosticsSnippetInfo.json file in my previous article. I’m using the most recent file Glenn’s diagnostic query files, but you can use whatever set of diagnostics you need for the version of SQL Server you’re investigating.

XML SQL Prompt snippet files

You may need to convert a JSON Snippet Collection to old-style XML Prompt snippets, for users who are stuck on older versions of SQL Prompt.

JSON SQL Prompt snippet files

With the following PowerShell script, you can convert any collection to new-style (v10.6 and later) JSON SQL Prompt snippet files. Again, I’m using JSON collection of Glenn Berry’s DMV scripts as the example.

Conclusions

It is useful to have a system that allows you to hoard clever or useful SQL code snippets that you come across, or to use public domain code libraries. Not only does it allow you to stop re-inventing ways of doing things, but it also is a great way of learning and improving skills. SQL Prompt provides an excellent way of using snippets, but it is up to you to make the best possible use of this feature to provide the snippets that are useful for you to prevent repetitive typing or, in my case, a memory that has a too-efficient garbage collection.

Tools in this post

SQL Prompt

Write, format, and refactor SQL effortlessly

Find out more