If you code in any programming language, the chances are that you’re constantly repeating similar code. There are some queries that are more common than others, such as the ones Grant Fritchey collected some time ago. He also showed how to accelerate them by using SQL Prompt. That’s great, but what happens when we don’t have an external tool?

Fortunately, since SQL Server 2012, SSMS is able to work with code snippets natively. It’s not the best option, but it’ll certainly help you on your day to day job and it’s free.

How to use them?

There are two types of snippets:

• Expansion snippets will simply insert text in the position of the cursor or replace selected text.
• Surround With snippets will insert text before and after the cursor or selected text.

Depending on what you’re trying to do, you’ll use one type or another. There are several snippets already available which allow you to easily create objects (procedures, indexes, users, synonyms, etc.) or control the flow (if, while). The fastest way to use them is by the keyboard shortcuts Ctrl+K,Ctrl+X for eXpansion snippets and Ctrl+K,Ctrl+S for Surround with snippets. You also have the option if you right click where you want to insert the snippet or through the Menu Edit->Intellisense->Insert Snippet…, as shown below.

However you’ve decided to insert the snippet, you’ll get something like this.

You just need to navigate through the folders and select the desired snippet. Depending on the selected snippet, you’ll have the ability to fill some fields. In the following example, you can define the schema, name and parameters for the function.

Customizing the snippets

The default snippets might not be exactly what you need, which is exactly why you can create your own. Snippets are simply xml files with a special extension and, as such, they have a basic structure that I’ll explain using the following example:

<?xml version="1.0" encoding="utf-8" ?>
<CodeSnippets  xmlns="http://schemas.microsoft.com/VisualStudio/2005/CodeSnippet">
    <CodeSnippet Format="1.0.0">
        <Header>
            <Title>DropCreateProc</Title>
            <Shortcut></Shortcut>
            <Description>Drops and Creates a Procedure and assigns permissions</Description>
            <Author>Luis Cazares</Author>
            <SnippetTypes>
                <SnippetType>SurroundsWith</SnippetType>
            </SnippetTypes>
        </Header>
        <Snippet>
            <Declarations>
                <Literal>
                    <ID>ProcedureName</ID>
                    <ToolTip>Name of the Procedure</ToolTip>
                    <Default>ProcedureName</Default>
                </Literal>
            </Declarations>
            <Code Language="SQL">
                <![CDATA[IF OBJECT_ID(N'[dbo].$ProcedureName$', N'P') IS NOT NULL
    DROP PROCEDURE $ProcedureName$
GO $end$$selected$
GO
GRANT EXECUTE ON [dbo].$ProcedureName$ TO [WebUser] AS [dbo]
GO
        ]]>
            </Code>
        </Snippet>
    </CodeSnippet>
</CodeSnippets>

First we find the XML declaration tag that specifies the version and encoding. This is strongly recommended for any XML file.

Then we find the CodeSnippets tag which will encapsulate all the snippet definition and contains the namespace for snippets.

Inside the CodeSnippets tag, we find the CodeSnippet with a Format attribute and two main tags. Don’t be fooled thinking that you can create several snippets in a single file. Only one snippet can go on each file even if the wording might suggest otherwise.

The Header section has the Title, Shortcut, Description, Author and Snippet Type. All of these are used to identify each snippet in the Code Snippets Manager or when inserting a snippet in the text editor. Note: Up to SSMS 2016 CTP 3.3, there’s no native functionality for shortcuts, using add-ins compatible with these snippets you could insert the snippets by writing a few characters.

The Snippet section has the actual code of the snippet. It’s composed by two parts Declarations and Code.

The Declarations section will allow you to declare variables (called literals) which you can modify after inserting the snippet.

• The ID is the way to identify the literal and should not be repeated.
• The Tooltip will give an indication of what should the literal be when the literal is selected.
• The Default is the text that will show up in the snippet when inserted.

In the Code section, you define the Language (SQL or XML) and insert the snippet’s code between “<![CDATA[“ and “]]>”. Two reserved words are available for use in the text of the Code element: $end$ and $selected$.

•      $end$ marks the location to place the cursor after the code snippet is inserted.
• b.      $selected$ represents text selected in the document that is to be inserted into the snippet when it is invoked. This is used for Surround With snippets.

If necessary, you could change the default delimiter with the attribute Delimiter in the Code tag.

The reserved words and the literals have a different behavior. For literals, when modifying its value, all instances are changed to that value. For the reserved words, only the last instance is considered.

Adding Custom Snippets

If you always use default paths to store your files, there’s a predefined location for snippets which you could use in %HOMEPATH%\Documents\SQL Server Management Studio\Code Snippets\SQL\My Code Snippets. Any snippet with the correct format and extension stored in that path will be automatically available to use.

But being serious, if you’re organized, you’ve probably created a nice folder structure to organize your snippets. To add those snippets, you’ll have to use the Code Snippets Manager available from the Tools Menu.

In here, you’ll be able to review all the snippets available for immediate use. It’s important to know, that SSMS registers folders and not specific files.

The Add button will allow you to use custom folders. When you add a folder, all the folders contained in it will be automatically added. This allows getting an easy way of organizing all of our snippets.

If, by any chance, you have a snippet stored in a folder that’s not included, you can Import it (with the Import button) and a copy will be created in any of the available folders that you select.

Conclusion

This is a great option that has been available for some years now, but I’m not sure if it has been widely used. As mentioned, some products will ease this by using snippets wizards and shortcuts, but for those that can’t afford those tools; this will improve your day to day work.

I’m sharing some of my snippets, but feel free to share any ideas you might have for a good use of this tool.

References

Code Snippets Schema Reference https://msdn.microsoft.com/en-us/library/ms171418.aspx

Add Transact-SQL Snippets https://msdn.microsoft.com/en-us/library/gg492130.aspx