Coding Snippets in SSMS (Microsoft SQL Server)

Hi all,

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:

Image showing how to access the Code Snippets Manager.

Access Code Snippets Manager.

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):

Image showing the Code Snippets Manager before Import.

Code Snippets Manager before Import.

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.

Image showing the MSSQL Server Code Snippets Manager Import Dialog.

SSMS Code Snippets Manager Import Dialog.

You should then be able to see your snippet under the nominated folder:

Image showing the Code Snippets Manager after Import.

Code Snippets Manager after Import.

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:

Image showing a Snippet before being expanded.

Snippet before Expansion.

Hit Enter to select your snippet; you should then see the following:

Image showing a Snippet after being expanded.

Snippet after Expansion.

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:

Shows the results of adding a Custom Snippets Folder.

Adding a Custom Snippets Folder.

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:

Surrounds With Snippet Example

I hope this has been helpful. Please comment below if you have any thoughts!

Cheerio!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s