Code Snippets are not a new thing. Many languages have them and many plugins for SQL Server Management Studio (SSMS) include them. However I believe the out of the box offering in SSMS is just fine and this post explores how I use the feature and how you could too.
Code Snippet Types
Firstly it is important to understand that within SSMS you have two different types of code snippet. These are:
- Expansion – offering the insertion of a code block into whatever location you put the cursor within a query window.
- SurroundsWith – as the name suggests this will encapsulate code you have highlighted within a query window adding top and tail code blocks from the snippet.
In each case the snippets will only add code to your query window, nothing will be overwritten.
What You Already Have
In SSMS you already have a wealth of code snippets built into the SQL Server client tool and what you may not realise is that when you, for example, right click in the object explorer panel for a new stored procedure, the resulting query window code is taken from a code snippet stored with the SSMS program files.
So that’s just one way to access code snippets within the context of whatever new view, function or procedure you create. The other way is via the SSMS keyboard shortcuts, which as a result of my extreme dislike for the defaults they are now more memorable to me than Ctrl + C and Ctrl + V. If I was ever to actually change them in SSMS options I’d probably be completely lost. Anyway, there are different shortcut combinations of each snippet type.
- Expansion: Ctrl + K, Ctrl + X
- SurroundsWith: Ctrl + K, Ctrl + S
In a new query window when pressed they will give you an IntelliSense style selection list which you can browse through with the arrow keys. Plus Enter and Backspace for forwards and backwards navigation.
The next thing to note about the standard set of SSMS code snippets is that they are stored at the following location (depending on your version of SQL Server/Visual Studio) should you ever wish to copy or modify them. Which if you continue reading you definitely will.
C:\Program Files (x86)\Microsoft Visual Studio 14.0\Common7\IDE\Extensions\Microsoft\SQLEditor\Snippets\1033
Finally if you care to search your C drive for files with a .snippet extension you will probably be surprised to learn that in the Microsoft world they are used all over the place. Especially if you have full Visual Studio installed. There implementations might vary between coding environments and languages but as a standard they are well established.
Creating Your Own SQL Snippets
Now that we know all about code snippets its time we think about creating our own. This is something I have done and encouraged when working with various SQL Server development teams over the years and every time the practice has been happily embraced. At the end of the day who can really remember the exact syntax for every language encountered on a daily basis… English is ‘ard enough 🙂
Right then, like a lot of Microsoft files underneath the pretty icon and file extension its just XML, not JSON yet, certainly in SQL Server land, RDL, DTSX etc. Well code snippets are no exception and if you have Notepad++ installed when you open the .snippet files syntax highlighting works a treat. So if you care to create a new Notepad file either with ++ or without (your choice) and paste in the following XML. Then save the file to a suitable location with the .snippet extension you’ll have created your first custom SQL code snippet. This is doing nothing more than printing the string Hello World.
<?xml version="1.0" encoding="utf-8" ?>
<CodeSnippets xmlns="http://schemas.microsoft.com/VisualStudio/2005/CodeSnippet">
<CodeSnippet Format="1.0.0">
<Header>
<Title>Hello World</Title>
<Shortcut></Shortcut>
<Description>My first Hello World SQL Server code snippet.</Description>
<Author>Paul Andrew</Author>
<SnippetTypes>
<SnippetType>Expansion</SnippetType>
</SnippetTypes>
</Header>
<Snippet>
<Declarations></Declarations>
<Code Language="SQL"><![CDATA[PRINT 'Hello World']]>
</Code>
</Snippet>
</CodeSnippet>
</CodeSnippets>
Within the above XML you can fairly easily make out the important values which you’ll want to change.
- Title – this is what will appear in your IntelliSense style list within SSMS.
- Description – this will also appear in SSMS next to the highlighted snippet title within the IntelliSense menu as a callout.
- Author – being me in the case of the example above.
- SnippetType – this is important to enter depending on the type of snippet you want as described above (Expansion or SurroundsWith).
Lastly you’ll want to edit the actual SQL script that has been included in the snippet. This is done within the CDATA block of the Snippet parent node and wrapped with square brackets, XML syntax highlighting will help here if available.
Snippet Use and Sharing
To add your custom snippets to SSMS go to Tools > Code Snippets Manager. Then in the dialogue that appears use Add and select the location where your snippet(s) are saved and choose Select Folder.
This snippet location does not need to be a local folder. It could also be a UNC path that everyone in the development team have configured giving them access to custom created shared code directly in SSMS. No need for DropBox shares or separate SQL files in mapped network drives.
I would also recommend defining this snippet location with a set of Visual Studio team settings. But maybe that’s for another blog post.
Here’s the Code Snippets Manager screen where you Add and Select Folder just in case its unclear.
Finally if you’ve done everything right or I’ve told you correctly, when you hit that “well known” keyboard shortcut Ctrl + K, Ctrl +X you’ll see the My Snippets folder (if you called it that) in the IntelliSense menu. Upon selection the callout description will appear and if you go all the way the SQL script will be added to your query window. I did both in the following screen shot just for demonstration purposes.
In a later blog post I’ll include my database backup and restore code snippet files which I use every day. Why just have a SQL file when the snippet file can give you so much more and in a more accessible way?
Many thanks for reading.
