Russ Thomas – SQL Judo

The Art of SQL Server Database Administration, Development, and Career Skills for the Technically Minded

SSMS Day 7: Leveraging Snippets

31 Days of SSMS: Table of contents

Yesterday we talked about Templates. Today let’s compare and contrast Snippets.

In truth there are a lot of similarities and just a little overlap in what SSMS offers in Templates and Snippets. Snippets however add a few extra features that for a long time folks complained were missing with Templates. They also offer a lot more in the way of interactivity and flexibility.

To demonstrate what I mean by this let’s jump right into a usage example. In a blank query window hit Ctrl+K and then Ctrl+X to bring up the Snippet prompt. You can also right click your mouse and select ‘Insert Snippet’ from the menu.




I selected the Snippet for creating a Stored Procedure with Output as shown below. Seems pretty similar to a template at first glance. A cool feature of snippets however is anytime you replace one of the highlighted values in the Snippet, SSMS will replace the matching values as well. So, for example, if I replace @param1 with @p1, SSMS will update all usages of that parameter for me within the context of the snippet.

You can also simply tab through each of the highlighted areas and replace as you go. What you don’t get is the Insert Parameter (Ctrl+Shift+M) dialog that comes with Templates but most people find the Snippet interface a little more fluid anyway.


Creating a Snippet Library:

Another feature that Snippets offers above templates is that when adding your own, you aren’t limited to the local folder designated by Templates. You can import Snippet definitions from anywhere, including a file share, which makes them great for sharing code libraries between a team of developers or administrators.

Managing Snippets:

You manage Snippets via the Code Snippet Manager. This can be accessed from the Tools menu under Tools->Code Snippet Manager.

Learning you have the ability to Import new Snippets and share Snippets almost always sparks the question… How do I create my own Snippets? In short, snippets are simply XML files that follow a specific syntax for identifying properties and parameters that make up the Snippet definition. A good way to learn how to write your own is simply opening an existing one and getting used to the format of the Snippet definition file. They are a lot to look at at first but once you get used to them, they aren’t so bad.

An even easier way to write your own is to follow this TechNet guide that takes you through it step by step.

Surround With:

While on the topic of Snippets. This is a great time to point out the Surround With Option just below it on the right click menu that functions much the same way. It’s usually faster to use the hotkey combination Ctrl+K then Ctrl+S.


This gives you a quick way to surround a block of code you’ve already written with a BEGIN END, IF, or WHILE statement. Just highlight the block of code you’d like to surround before selecting the snippet.

That pretty much sums up what SSMS offers in the way of pre-generated code. Tomorrow we’ll jump into debugging code with SSMS.

Leave a Reply

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

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

Google photo

You are commenting using your Google 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 )

Connecting to %s

%d bloggers like this: