Continuing on with the idea of coding snippets, here’s a follow up showing the (very similar) procedure for creating snippets in SQL Server Management Studio (SSMS).
As before, you’ll need a file with the ‘.snippet’ extension to begin, which contains a snippet in an XML format. A basic example is shown below for reference, encapsulating some code that can quickly search a Stored Procedure definition for a particular search term:
<?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>Sys.procedures SQL Check</Title> <Shortcut></Shortcut> <Description>Creates SQL to check sys.procedures for a literal string.</Description> <Author>Lewis Grint</Author> <SnippetTypes> <SnippetType>Expansion</SnippetType> </SnippetTypes> </Header> <Snippet> <Declarations> <Literal> <ID>SearchTerm</ID> <ToolTip>The term to search for.</ToolTip> <Default>SearchTerm</Default> </Literal> </Declarations> <Code Language="SQL"> <![CDATA[--Check SP content for a literal string SELECT name FROM sys.procedures WHERE OBJECT_DEFINITION(OBJECT_ID) LIKE '%$SearchTerm$%'; ]]> </Code> </Snippet> </CodeSnippet> </CodeSnippets>
As you can see, this file takes the familiar ‘.snippet’ format, supporting Snippet, Code and Declarations elements. These include the code snippet itself, and placeholder definitions, as before. You will also find the Header element, where pertinent information about the snippet can be stored, such as author and description.
Easily recognisable territory so far, I’ll think you’ll agree.
Within SSMS, much as in Visual Studio, navigate to Tools > Code Snippets Manager (Ctrl + K, Ctrl + B) to get underway:
Again, pretty much plain sailing from here on in. You’ll have the same options as in Visual Studio to import a snippet file (just use the Import button):
Navigate to your snippet file and select it. You’ll now have the opportunity to map the snippet to a nominated folder. In this instance, we’ll use the My Code Snippets folder. Click Finish to complete the process.
You should then be able to see your snippet under the nominated folder:
That’s it, you’ve imported the snippet! You can now open a new query and use Ctrl + K, Ctrl + X to launch the Code Snippet Picker, whereby you can use the arrow keys to move between folder and snippets:
Hit Enter to select your snippet; you should then see the following:
Then you can start using your code, utilising tab to cycle through any placeholders. Another trick, that I’ve used, is to Add an entire folder of snippets using the Code Snippets Manager (instead of using Import). Using a ‘tactically’ named folder, you can gain faster access to your snippets:
This type of snippet is an Expansion snippet. You can write other kinds, such as the Surrounds With snippet, an example of which can be found here on MSDN, illustrating a try/catch construct:
I hope this has been helpful. Please comment below if you have any thoughts!