I choose a lazy person to do a hard job. Because a lazy person will find an easy way to do it
Bill Gates
The work of a DBA sometimes involves very repetive tasks:
- Create a new login / users, or add/remove permissions
- Run manual index / stats operations when the scheduled maintenance tasks are not enough and need a manual “push”
- Taking a backup or restore one to troubleshoot issues or restore missing data…
There are many ways to accomplish this:
You can keep a folder full of scripts and just open the one you need and run it
Use a third-party tool to store all those scripts and paste them with a quick shortcut or a few clicks
Or the one I’ll be discussing, using snippets
Snippets are a component integrated into SSMS, and includes some defaults to create tables, functions, stored procedures… These defaults can be great by saving you a few seconds when it’s been a while and you don’t remember the exact syntax for specific objects, but they are very basic
The good thing?
You can create your own
The bad thing?
The documentation tells you how to import them, but no how to create them
Building your first snippet
The snippets follow a XML format with very few values to modify:
| Title | The name of the snippet, as loaded by SSMS |
| Shortcut | Not used in SSMS |
| Description | Tooltip when selecting the snippet |
| Author | To keep track who wrote it |
| SnippetTypes | Expansion/SurroundsWith (Explained later) |
| * Literal \ ID | Name of a variable used in the snippet |
| * Literal \ ToolTip | Tooltip for the variable |
| * Literal \ Default | Default value for the variable |
*Literals (or variables) are not required, and the whole component can be removed if not deemed necessary)
Let’s start with the fact that there are two types of snippets: Insert snippets & Surround snippets.
“Insert” snippets (SnippetType: “Expansion”)
These snippets work like a “copy & paste”. Here is a template for them (sorry for the poor formatting: something wrong with some of my WordPress pulings)
<?xml version="1.0" encoding="utf-8" ?>
<CodeSnippets xmlns="http://schemas.microsoft.com/VisualStudio/2005/CodeSnippet">
<_locDefinition xmlns="urn:locstudio">
<_locDefault _loc="locNone" />
<_locTag _loc="locData">Title</_locTag>
<_locTag _loc="locData">Description</_locTag>
<_locTag _loc="locData">Author</_locTag>
<_locTag _loc="locData">ToolTip</_locTag>
</_locDefinition>
<CodeSnippet Format="1.0.0">
<Header>
<Title>Short name of the script</Title>
<Shortcut></Shortcut>
<Description>Blah blah blah...</Description>
<Author>SQLozano.com</Author>
<SnippetTypes>
<SnippetType>Expansion</SnippetType>
</SnippetTypes>
</Header>
<Snippet>
<Declarations>
<Literal>
<ID>VariableName1</ID>
<ToolTip>Description of the variable #1</ToolTip>
<Default>Default value for the variable #1</Default>
</Literal>
<Literal>
<ID>VariableName2</ID>
<ToolTip>Description of the variable #2</ToolTip>
<Default>Default value for the variable #2</Default>
</Literal>
</Declarations>
<Code Language="SQL">
<![CDATA[
PRINT '$VariableName1$'
SELECT '$VariableName2$'
]]>
</Code>
</Snippet>
</CodeSnippet>
</CodeSnippets>
Each “Literal” block represents a variable that will be highlighted in your code, including the tooltop and the default value
This type of snippet is useful when there are no variables to set and is just a “paste & run”, or when there are multiple variables to adapt for the current execution (TAB can be used to move through all the variables in the script)
“Surround with” snippet (SnippetType: “SurroundsWith”)
These snippets are divided in two blocks: just highlight a piece of your code and it will past a block before and the other after your code
<?xml version="1.0" encoding="utf-8" ?>
<CodeSnippets xmlns="http://schemas.microsoft.com/VisualStudio/2005/CodeSnippet">
<_locDefinition xmlns="urn:locstudio">
<_locDefault _loc="locNone" />
<_locTag _loc="locData">Title</_locTag>
<_locTag _loc="locData">Description</_locTag>
<_locTag _loc="locData">Author</_locTag>
<_locTag _loc="locData">ToolTip</_locTag>
</_locDefinition>
<CodeSnippet Format="1.0.0">
<Header>
<Title>Short name of the script</Title>
<Shortcut></Shortcut>
<Description>Blah blah blah...</Description>
<Author>SQLozano.com</Author>
<SnippetTypes>
<SnippetType>SurroundsWith</SnippetType>
</SnippetTypes>
</Header>
<Snippet>
<Declarations>
<Literal>
<ID>VariableName1</ID>
<ToolTip>This goes before your code</ToolTip>
<Default>Before</Default>
</Literal>
<Literal>
<ID>VariableName2</ID>
<ToolTip>This goes after your code</ToolTip>
<Default>After</Default>
</Literal>
</Declarations>
<Code Language="SQL">
<![CDATA[
PRINT '$VariableName1$'
$selected$$end$
PRINT '$VariableName2$'
]]>
</Code>
</Snippet>
</CodeSnippet>
</CodeSnippets>
This type of snippet is useful when there is only one variable to be reused (think of a script to get the backup history from a database, or query a table searching for a specific key)
What are the possible uses for snippets?
As a DBA
Many repeating operations can be turned into snippets:
- Granting a list of permissions to the same login/user using a SurroundWith snippet so you only need to type the login/user and highlight it
- Rebuild an index with the same settings over and over using an Insert snippet so you only need to replace the variables with the database, table & index names
- Execute troubleshooting queries with an Insert snippet when time is critical and you don’t want to waste it searching for the necessary scripts online or in your local folder, or remember which DMVs can be joined with with DMVs or the columns used for that
As a Developer
You can ensure all your tables have the same format (if you are the type of developer that wants to make sure data types are upper case)
- Reuse small pieces of code such as error handling to ensure every stored procedure you write follows the same standard
- Querying data so you don’t have to remember how all data is structured and you can reuse complex queries and reports
As a Team
Whether you work as a DBA or as a Developer, it is likely you’ll be part of a team, and it is important to maintain consistency to avoid scenarios like these:
- Members of the DBA team troubleshooting an issue but each of them querying different DMVs or paying attention to different columns, leading to ignoring some indicators or even reaching different conclusiones
- Developers using different naming or formatting conventions, making it harder to review another developer’s code
- Developers reviewing a reporting problem but each having a different query with different filters or calculations, not sharing the same vision as the other members of the team
How to achieve it? Using a code repository.
The way SSMS loads snippets is by selecting a folder and loading any snippet contained in it, including subfolders
The snippets can be selected in the same way the folders are organized
And the best thing: if a snippet changes, you don’t need to do anything in SSMS: the contents of that folder are loaded every time you press the shortcut to insert a snippet, no need to restart SSMS, open a new query window…
When using a repo, you only need to run the usual “git pull” to get the updated version or “git checkout xxxxx” if you want to swtich between different branches of the same repo containing different versions (like having a branch for SQL Server 2000 instances and another for newer ones like 2019-2022…)
But what if I’m using Azure Data Studio
SSMS snippets can be converted for use in Azure Data Studio (which uses JSON rather than XML), but Azure Data Studio is being discontinued by MS.
However, for anyone that still wants to use it (or needs to use it, since SSMS is not available for Linux or Mac computers), here is a PowerShell script to make that conversion
$AllSnippets = Get-ChildItem -Recurse -Filter "*.snippet" | Where-Object {$_.Directory.name -ne "Templates"}
$AllSnippets.Fullname
$JsonSnippets = @{}
foreach($Snippet in $AllSnippets){
[xml]$xmldata = Get-Content -Path $Snippet.Fullname
$title = $xmldata.CodeSnippets.CodeSnippet.Header.Title
$prefix = $Snippet.Directory.Name + '_' + $title.Replace(' ','')
$description = $xmldata.CodeSnippets.CodeSnippet.Header.Description
$body = ($xmldata.CodeSnippets.CodeSnippet.Snippet.Code.InnerXml).Replace('<![CDATA[','').Replace(']]>','')
$literalcounter = 1
foreach($literal in $xmldata.CodeSnippets.CodeSnippet.Snippet.Declarations.Literal){
$body = $body.Replace('$'+$literal.ID+'$','${'+$literalcounter+':'+$literal.Default+'}')
$literalcounter += 1
}
$SnippetJson = @{
$title = @{
'prefix' = $prefix
'description' = $description
'body' = $body
}
}
$JsonSnippets += $SnippetJson
}
$JsonSnippets = ConvertTo-Json($JsonSnippets)
$JsonSnippets = $JsonSnippets.Replace('}\n{',',')
$JsonSnippets = $JsonSnippets.Replace("\u0027","'")
if ($IsWindows){
$Destination = $env:USERPROFILE + '\AppData\Roaming\azuredatastudio\User\snippets\sql.json'
} elseif ($IsLinux){
$Destination = $env:HOME + '/.config/azuredatastudio/User/snippets/sql.json'
} elseif ($IsMac){
$Destination = $env:UserName + '/Library/Application Support/azuredatastudio/User/snippets/sql.json'
}
$JsonSnippets | Out-File $Destination -Encoding utf8
This script can be executed in the folder where all our snippets are and will convert and copy them into the location ADS uses.
