Managing SQL Snippet Collections using JSON and PowerShell
Maintain your favorite SQL snippets and queries centrally, using PowerShell to save and update each snippet collection, in JSON, and then converting them into SQL code snippets for SQL Prompt.
Why SQL Code Snippets?
Every DBA and Developer likes to maintain various collections of useful SQL code snippets, queries and clips, each for a particular purpose. For example, we might have one directory containing metadata queries for exploring SQL Server databases and another for diagnostic queries to troubleshoot performance, and another with template and snippets for creating tables, views, indexes and so on. A DBA might also have a directory full of snippets just for maintaining Service Broker.
A SQL code snippet, as we’re defining it here, is any of these, but stored in a particular form that means we can insert the required snippet directly into a query pane, within SSMS.
SSMS template and snippets provides some built-in ‘boilerplates’ for creating various types of database object. SQL Prompt also provides a set of built-in code snippets in a more utilitarian form, with the advantage of allowing replacement points (variables) and allowing you to insert any pre-defined block of code instantly, at the current cursor position in a query pane. For various examples of useful snippets, see these SQL Code Snippet articles.
Programmers can speed up development time by using code snippets instead of wasting time typing repetitive code or ‘googling’ for samples.
Maintaining a central collection of snippets
What most developers and DBAs would like is to be able to manage all their different collections in one place, and then have instant access to all their useful snippets and queries, when developing code or troubleshooting in SSMS. This would save a lot of time spent in hunting in various places for the right version of the script we need. We can extend SQL Prompt’s built-in collection of snippets simply by adding any new ones via the GUI. See, for example: Creating a SQL Code Snippet in SQL Prompt and SSMS. However, this approach doesn’t scale.
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 diagnostic (DMV) queries. They are the state of the art in diagnosing problems with SQL Servers and databases. Also, 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 at time of writing. In such cases, the SQL Prompt interface soon gets impractical as the sole way of importing them, and then keeping them up to date. 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.
I’ll show you, instead, how you can take existing code examples and scripts, such as SSMS templates, or Glenn Berry’s collection of diagnostic queries, or your own favorite collection of SQL queries or fragments and use PowerShell to save them as JSON collections. Once you have all these saved in a neutral JSON format, you then just need a single PowerShell script to chop them up into individual Prompt snippet files. The idea is that you can then extend the built-in SQL code snippets to which you have direct access, within SSMS, to include all your most-used metadata, DDL and diagnostic queries.
You can add your snippet files either to SQL Prompt’s default snippet directory or to a custom directory. To switch from one directory to another, simply alter 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.
Managing SQL code snippets from multiple sources
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.
I’ll demo 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’ll demo 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. The result in each case is a JSON file consisting of an array of JSON documents, one per snippet.
Another possible source of snippets, though I won’t demo it here, is a programmer’s clipboard that allows you to make collections of clips; I use AceText. 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 Prompt snippet collection.
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. Depending on your version of SQL Prompt, you can save them in their new JSON format or as the Classic XML files, which are almost to the same standard format as Visual Studio Snippets.
The following diagram shows some of the sources and destinations for SQL code snippets. Only the processes represented by solid arrows are shown in this article:
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 in a later 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.
Code snippet formats
The original XML format for SQL Prompt code snippet was (supported in SQL Prompt up to version 10.5) was designed to be able to hold a collection of snippets in a single file. However, it never did. The new JSON snippet format (v10.6 and later) in has no ambitions to hold a collection, just a single snippet.
XML format
The snippet is stored and distributed in a standard XML file format for Visual Studio. This format was defined in 2005. 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.
XML 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):
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
<?xml version="1.0" encoding="utf-8"?> <CodeSnippets xmlns="http://schemas.microsoft.com/VisualStudio/2005/CodeSnippet"> <CodeSnippet Format="1.0.0"> <Header> <Title></Title> <Shortcut></Shortcut> </Header> <Snippet> <Code Language=""> <![CDATA[]]> </Code> </Snippet> </CodeSnippet> </CodeSnippets> |
The XML Prompt snippet format was a slightly different format to the VS Standard, and it therefore has a different filetype.
JSON format
The new JSON format is much simpler but no longer conforms to any industry standard. It is unusual in that it contains a GUID which is important to preserve as it is used by SQL Prompt. Otherwise, it retains only the prefix(shortcut), description, body(code) and placeholders. Here’s an example JSON snippet for a commentblock snippet.
1 2 3 4 5 6 7 8 9 10 11 12 |
{ "id": "411e425a-6faa-4944-9b6f-1f111e466ad9", "prefix": "commentblock", "description": "Insert a mutli-line comment", "body": "/***************************************************\n$CommentHere$\n***************************************************/\n", "placeholders": [ { "name": "CommentHere", "defaultValue": "" } ] } |
Converting your SQL Scripts and snippets into JSON
As discussed earlier, each collection needs a separate PowerShell scripts to convert it into a neutral JSON format. I’ll provide example scripts for converting the SSMS Templates and Glenn Berry’s diagnostic scripts.
A word of caution before we dive in. 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.
You should also be aware of a few incompatibilities, when converting between different types of snippet. 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
I’ll start with a PowerShell script to covert a whole set of SSMS templates into a JSON collection. Once your templates, or any other collection of snippets or queries, are in a neutral JSON format, we just need a single PowerShell script to convert any of them into Prompt snippets.
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.
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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 |
<# extracting the SSMS SQL Templates into a JSON archive it takes all the .SQL files from a template directory and all its subdirectories and puts them into a single JSON collection file. It assumes that the name of the file is the title of the snippet. It creates the snippet name from the title by taking the first character of each word except where there are several SQL Server objects with the same first letter, in which case it assigns a two-letter acronym #> # this base directory for the templates will probably need to be changed for your setup. $global:WorkDirectory = 'MySnippetWork' $SnippetDirectory = "$($env:HOMEDRIVE)$($env:HOMEpath)\AppData\Roaming\Microsoft\" + 'SQL Server Management Studio\14.0\Templates\SQL' # The location for the collection file $OutputFile = "$WorkDirectory\templateInfo.json" # we build up our collection from the pipeline $SnippetCollection = @(); # we need to know if a file is in a subdirectory as it might have a duplicate #$BaseDirectory=Split-path (Split-path $SnippetDirectory -Parent) -leaf $BaseDirectory = Split-path $SnippetDirectory -leaf # a bug in Get-childitem prevents it excluding a recursed directory Get-ChildItem -Path $SnippetDirectory -Directory -Recurse | where { $_.FullName -inotmatch 'EARLIER VERSIONS' } | foreach { get-childitem -file "$($_.FullName)\*.sql" } | Foreach { $CurrentFile = $_ # the pipe has delivered us the file $SnippetName = $CurrentFile.BaseName #name of template $SnippetType = $CurrentFile.directory.Name #Type of tempate $shortcut = '' #check to see if we have recursed beyond the first level $TheFileBaseDirectory = (get-item $CurrentFile.DirectoryName).parent.Name #have we got a subdirectory? If so, take its' first name for the shortcut # and add it to the description (e.g. Azure) $possiblePrefix = ''; #by default if ($TheFileBaseDirectory -ne $BaseDirectory) #in a subdirectory { $shortcut = $TheFileBaseDirectory.TrimStart()[0]; $possiblePrefix = "$TheFileBaseDirectory - " }; #now create the shortcut from the first character of each word # with exceptions for duplicates and taking out short words if ($SnippetType -eq 'Application') { $shortcut += $SnippetType[0]; } $first = $false #was true $SnippetName.split(' ') | where { $_ -notin @('in', 'to', 'a', 'for', 'and', '(New', 'on', 'with') } | foreach{ $shortcut += Switch ("$_") { <#A#> 'Aggregate' { 'Ag' } 'Assembly' { 'As' } <#C#> 'Credential'{ 'cr' } 'Certificate'{ 'Ce' } 'Cleanup'{ 'Cl' } 'Capture'{ 'Ca' } 'Constraint'{ 'Cn' } 'Column'{ 'Cl' } <#D#> 'Drop'{ 'DR' } 'Detach'{ 'De' } 'Default'{ 'Df' } <#F#> 'Federation' { 'Fe' } 'Filetable' { 'Fi' } 'Full-Text'{ 'FT' } <#P#> 'Private' { 'Pr' } 'Public'{ 'Pu' } 'Path'{ 'Pa' } 'Property'{ 'Po' } <#R#> 'Route'{ 'Ru' } 'Role'{ 'Ro' } <#S#> 'Start'{ 'Sa' } 'Stop'{ 'So' }'Service'{ 'Se' } 'Schema'{ 'Sc' } 'Stored'{ 'St' }'Statistics'{ 'Sx' } <#T#> 'Trigger' { 'Tr' } 'Type' { 'Ty' } Default { $_[0] } } } #now we get the template file $SnippetBody = [IO.File]::ReadAllText($CurrentFile.FullName) #add the snippet into the collection $SnippetCollection += @{ Header = @{ Title = $SnippetName; Shortcut = $shortcut; Category = "$SnippetType"; Source = "SSMSTemplate"; Description = "$SnippetName ($($possiblePrefix)$SnippetType)"; Author = 'Microsoft'; SnippetTypes = @{ SnippetType = 'Expansion' } }; Declarations = @() Snippet = @{ Code = $SnippetBody } }; } #check that they have unique shortcuts $SnippetCollection | foreach{ $_.Header.Shortcut } | Group-Object | Where-Object { $_.Count -gt 1 } #save to disk $SnippetCollection | convertto-json -depth 10 >$OutputFile |
Converting Glenn Berry’s DMV queries in JSON
Glenn Berry 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.
With the following PowerShell script, you can convert the most recent file Glenn’s diagnostic queries, or whatever set of diagnostics you need for the version of SQL Server you’re investigating, into a JSON snippet collection file. You can then easily keep the whole collection updated, as Glenn makes improvements.
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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 |
<# Convert a Glenn Berry diagnostics file to a snippet collection file. Fortunately he uses a very structured way of ordering the queries, and so it is very easy to chop them up. The hardest task was creating and testing the Regular expression to slice the file up. Regex Buddy helped a lot #> $global:WorkDirectory = 'S:\work\programs\SSMS' #you'll need to fill this in with the name of the file $filename = 'SQL Server 2017 Diagnostic Information Queries' #you may need to amend this to the path, and make sure that the global variable # $WorkDirectory is defined. $FileAndPath = "$WorkDirectory\$filename.sql" #This will be where you store the data. Change the file by all means $OutputFile = "$WorkDirectory\DiagnosticsSnippetInfo.json" # read the contents in one gulp. $ScriptFile = [IO.File]::ReadAllText($FileAndPath) # the Regex looks for a line like the dummy one below as the break between queries with # interesting information such as number, title and description in it. It avoids running # past the beginning of the next query by using a positive lookahead $regex='-- (?<Name>.{10,}) \(Query.(?<Number>\d{1,3})\).\((?<Title>.{1,80})\)(?<Code>(?s:.){10,}?)(?=--.{1,100}\(Query)' #we need to have a false query at the end to get the final query ($scriptFile+' -- (Query 00) (End Of File)' | select-string $regex -allmatches).matches | foreach{ $TheMatch = $_; $TheTitle="$($TheMatch.Groups['Number'].Value) - $($TheMatch.Groups['Title'].Value)" @{ Header = @{ Title = $TheTitle; Shortcut = "GB$('{0:d2}' -f [int]$TheMatch.Groups['Number'].Value)"; Category = "Diagnostic"; Source = "$filename" Description = "$($TheMatch.Groups['Name'].Value)"; Author = 'Glenn Berry'; SnippetTypes = @(@{ SnippetType = 'Expansion' }) } Snippet = @{ Declarations = @{ }; Code = "--$TheTitle -- $($TheMatch.Groups['Name'].Value) $($TheMatch.Groups['Code'].Value)"; } } } | convertto-json -depth 10 >$OutputFile |
Converting JSON collections into Prompt Code Snippets
Once you have your JSON collection files, it is a reasonably simple matter to convert them into SQL Prompt Snippet Files. This requires one simple script (one for each format, XML or JSON). It is currently set up to import the SSMS templates JSON collection, but doing other types, like Glenn’s scripts, is just a matter of changing names.
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.
Converting to JSON SQL Prompt Snippet files
Prompt’s new format of JSON file is much easier to save to than XML. It is currently set up to import templates from the template collection, and you can just change the name to the collection you want to convert.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
#you need to create this 'WorkDirectory' variable if you haven't done so $global:WorkDirectory = 'S:\work\programs\SSMS' #you will need to change this to wherever you located and named the snippet file #$InputFile = "$WorkDirectory\TemplateInfo.json" $InputFile = "$WorkDirectory\DiagnosticsSnippetInfo.json" #$TypeOfSnippet='Templates' $TypeOfSnippet='PromptNewFormatDiagnostics' $DestinationPath="$WorkDirectory\$TypeOfSnippet" if (!(Test-Path -Path $DestinationPath)) #does it already exist? {$null=New-Item -Path $DestinationPath -ItemType directory} #if not, create it. #read the entire file in and convert it to a powerShell Object $TheInput=[IO.File]::ReadAllText($InputFile) | ConvertFrom-json $TheInput| foreach{ $SnippetData =$_ $GUID=new-GUID;<#GUID#> $WhereToStoreIt = "$DestinationPath\$($SnippetData.Header.Shortcut -replace '\s','')-$GUID.json" @{'id'=$GUID;<#GUID#> 'prefix'=$SnippetData.Header.Shortcut; 'Description'=$SnippetData.Header.Description; 'body'=$SnippetData.Snippet.Code}|ConvertTo-JSON > "$WhereToStoreIt" } |
The new JSON format only holds the prefix, body and description, so it is easy to transfer back into a JSON collection format. The mapping is shown in the code above. I would be inclined to store the GUID as a declaration. If you do this, then be sure to alter the above code to retrieve the GUID from the declaration!
Converting to XML SQL Prompt snippet files
If you’re still on SQL Prompt 10.5 or earlier, here’s the equivalent script to convert to the “classic” XML file format.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 |
#you need to create this 'WorkDirectory' variable if you haven't done so $global:WorkDirectory = 'S:\work\programs\SSMS' #you will need to change this to wherever you located and named the snippet file #$InputFile = "$WorkDirectory\TemplateInfo.json" $InputFile = "$WorkDirectory\DiagnosticsSnippetInfo.json" #$TypeOfSnippet='Templates' $TypeOfSnippet='PromptDiagnostics' $DestinationPath="$WorkDirectory\$TypeOfSnippet" if (!(Test-Path -Path $DestinationPath)) #does it already exist? {New-Item -Path $DestinationPath -ItemType directory} #if not, create it. #read the entire file in and convert it to a powerShell Object $TheInput=[IO.File]::ReadAllText($InputFile) | ConvertFrom-json $TheInput| foreach{ $SnippetData =$_ #$DeclarationsNode=$null #$xmlnode=$null #We create a blank file of the correct form and fill in the parameters #here is just the blank format $xmlDoc = [System.Xml.XmlDocument]'<?xml version="1.0" encoding="utf-8"?> <CodeSnippets> <CodeSnippet Format="1.0.0"> <Header> <Title/> <Shortcut/> <Description/> <Author/> <SnippetTypes> <SnippetType/> </SnippetTypes> </Header> <Snippet> <Declarations /> <Code Language="SQL"><![CDATA[ ]]> </Code> </Snippet> </CodeSnippet> </CodeSnippets> '; # we'll fill in the header first $Header = $xmldoc.CodeSnippets.CodeSnippet.Header # sql prompt doesn't use a separate title, just the shortcut # $Header.Title = $SnippetData.Header.Title $Header.Title = $SnippetData.Header.Shortcut #we create a suitable snippet file $WhereToStoreIt = "$DestinationPath\$($SnippetData.Header.Title -replace '\s','').sqlpromptsnippet" $Header.Shortcut = $SnippetData.Header.Shortcut $Header.Description = $SnippetData.Header.Description #$Header.Author = $SnippetData.Header.Author $Header.SnippetTypes.SnippetType = $SnippetData.Header.SnippetTypes[0].SnippetType $Snippet = $xmldoc.CodeSnippets.CodeSnippet.Snippet if ($SnippetData.Snippet.Declarations -ne $null -and $SnippetData.Snippet.Declarations.Literal -ne $null) {#we have to do this slightly awkwardly in order to get the format exactly right $DeclarationsNode = $Snippet.AppendChild($xmldoc.CreateElement('Declarations')) $SnippetData.Snippet.Declarations.Literal | foreach { $Literal = $DeclarationsNode.AppendChild($xmldoc.CreateElement('Literal')); $TheLiteral = $xmldoc.CreateDocumentFragment(); $TheLiteral.InnerXml = "<ID>$($_.ID)</ID><ToolTip>$($_.Tooltip)</ToolTip><Default>$($_.Default)</Default>"; $null = $Literal.AppendChild($TheLiteral); } }# now we add the code $Snippet.Code.InnerText = $SnippetData.Snippet.Code # and we save the file $xmlDoc.Save("$WhereToStoreIt"); #save it to the workDirectory } |
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.