SQL snippets in SQL Server Management Studio

Snippets are a great productivity feature to speed up typing of repetitive T-SQL. Snippets were introduced in SQL Server 2012 as part of SQL Server Management Studio (SSMS).

Snippets are XML templates with predefined fields and values that render into T-SQL, when processed by the query window.

In the example below is the results of the Create Trigger snippet that inserts T-SQL required to create a trigger in the query window at the current cursor position. The highlighted elements are placeholders that are replaced with values that the user inputs:

There are three categories of SQL snippets:

Default snippets
These SQL snippets come, out-of-the-box with SSMS and they are placed under folders based on category (e.g. Table, Function, Index).
Surround snippets
These three snippets, found in the Function folder, help to implement code structure like Begin End, While, and If around selected text in the query window.
Custom snippets
Users can create their own custom snippets. Here is an example of the “plumbing” underneath a custom SQL snippet.

…and here how it looks when is initiated in the query window.

By default snippets for SSMS 2016 are found under this path:

C:\Program Files (x86)\Microsoft SQL Server\130\Tools\Binn\ManagementStudio\SQL\Snippets\1033\

The location of SQL snippets depends on the version of the SSMS that is used. So if SSMS 2014 is used, the default path on which there snippets are: C:\Program Files (x86)\Microsoft SQL Server\120\Tools\Binn\ManagementStudio\SQL\Snippets\1033\

The only difference is in the name of the folder that come after the Microsoft SQL Server folder (e.g. folder 130 for SSMS 2016, 120 for SSMS 2014.)

To learn more about these folders please visit this link.

To save a SQL snippet the .snippet extension must be used

Inserting a built-in snippet

There are three ways to insert a built-in SQL snippet that comes with SSMS in the query window:

In the SSMS main menu under the Edit menu, choose the IntelliSense option and select the Insert Snippet command:
Right-click in the query window will show popup menu and from the list choose the Insert Snippet command:
Using the combination of keyboard shortcut Ctrl+K followed by Ctrl+X

Select a category e.g. Function to see all available snippets under that category:

Double click on the desired snippet from the list will insert code snippet in the query window with highlighted placeholders that need to be modified:

When a SQL snippet is in the query window, the position of the cursor is in the first highlighted placeholder (e.g. dbo). Enter a value and press the Tab key the cursor position will switch to the next highlighted placeholder (e.g. FunctionName) Inserting a surround SQL snippet

Inserting a surround SQL snippet

There are three ways to invoke a surround SQL snippet in query window. To start, first select the code that want to be surrounded in the query window and then use one of the three ways for invoking:

From the SSMS main menu, click the Edit menu and from the IntelliSense option select the Surround With command:
Right-click in the query window and from the popup menu choose the Surround With command:
Using the combination of keyboard shortcuts Ctrl+K followed by Ctrl+S

Any of these actions will result in appearance of the Surround With window with folders containing SQL snippets:

Select the folder to see all available surround SQL snippets from that folder:

Double clicking on the desired surround SQL snippet, will insert a snippet around the selected code. Also to insert a snippet in the query window the Tab or Enter keys can be used.

Below is the resulting text showing that the BEGIN and END keywords have been added above and below the selected text, respectively:

Create custom snippets

Custom snippets are created as text files in your favorite editor e.g. Notepad

To create a custom SQL snippet some rules must be followed:

First, every SQL snippet must start with an XML declaration tag and with specifies the version and encoding:

<?xml version=”1.0″ encoding=”utf-8″ ?>

The SQL snippet must have the CodeSnippets tag that encapsulates all the snippet definition and contains the namespaces for snippets

The CodeSnippet tag contains the Format attribute and two sub-tags (Header and Snippet)

The Header, sub-tag describes the characteristics of the code snippet, by entering text in the Title, Description, Shortcut, Author and SnippetType elements, which will be displayed in the Code Snippets Manager or when inserting the code in the query window:

Title displays the name of the snippet in the Code Snippets Manager windows and in the Insert Snippet context menu:
Description describes the purpose of the snippet, it is also displayed in the Code Snippets Manager window, and also shows as a tooltip when inserting a SQL snippet in the query window:
SnippetType defines which category the snippet belongs to e.g. Expression, SurroundsWith or Refactoring.

More details about a snippet types can be found on the SnippetType Element (IntelliSense Code Snippets) page.

<Header>
<Title>Create Table</Title>
<Shortcut></Shortcut>
<Description>Creates a table with one column.</Description>
<Author>Marko Zivkovic</Author>
<SnippetTypes>
<SnippetType>Expansion</SnippetType>
</SnippetTypes>
</Header>

The Snippet sub-tag contains the code of the SQL snippet and is composed of two sections: Declaration and Code.

The Declaration section allows declaration of variables (literals) which can be modified after inserting the snippet in the query window.

The example below shows how a SQL snippet looks with the default value for the name of the table (e.g. Table_Name) and how will look when user enters values for the name (e.g. MyNewTable):

Each variable contains a Literal element which has the following sub-elements:

ID is used to uniquely identify the literal.

In the case of a duplicate/repeated ID, the repeated value from a SQL snippet will not be highlighted, when the SQL snippet is invoked in the query window.

ToolTip is used to display the tool tip when the mouse cursor hovers over a highlighted variable.

Default is used as the default value for the variable that will show up in the snippet when inserted them in the query window

The Code section is used to define the code in the CDATA section that will be inserted from the query window. The Language attribute must be specified, which defines the processing language of the code snippet. To use in SSMS, in the Language attribute should be “SQL”:

The variables $SchemaName$, $TableName$ referring to the name of the IDs that are defined under the Declaration section.

$end$ is used for the location of the cursor after the code snippet is inserted.

Here is an example of a SQL snippet:

<?xml version=”1.0″ encoding=”utf-8″ ?> <CodeSnippets xmlns=”http://schemas.microsoft.com/VisualStudio/2005/CodeSnippet”>
<CodeSnippet Format=”1.0.0″>
<Header>
&emsp<Title>Create Table</Title>
<Shortcut></Shortcut>
<Description>Creates a table with one column.</Description>
<Author>Marko Zivkovic</Author>
<SnippetTypes>
<SnippetType>Expansion</SnippetType>
</SnippetTypes>
</Header>
<Snippet>
<Declarations>
<Literal>
<ID>SchemaName</ID>
<ToolTip>Name of the schema</ToolTip>
<Default>Customer</Default>
</Literal>
<Literal>
<ID>TableName</ID>
<ToolTip>Name of the table</ToolTip>
<Default>Table_Name</Default>
</Literal>
<Literal>
<ID>Column</ID>
<ToolTip>Name of the column</ToolTip>
<Default>Column_Name</Default>
</Literal>
<Literal>
<ID>DataType</ID>
<ToolTip>Data type of the column</ToolTip>
<Default>int NOT NULL</Default>
</Literal>
</Declarations>
<Code Language=”SQL”>
<![CDATA[CREATE TABLE $SchemaName$.$TableName$
(
$Column$ $DataType$
);$end$]]>
</Code>
</Snippet>
</CodeSnippet>
</CodeSnippets>

Registering a SQL snippet in SSMS

After creating the SQL snippet file, the SQL snippet file must be registered via the Code Snippets Manager in order to use it in SSMS. With the Code Snippets Manager, a folder can be added that contains multiple snippets, or by using the Import button from the Code Snippets Manager window to import one or more SQL snippets in the My Code Snippet folder.

Adding a snippet folder

To add a folder that contains a SQL snippet, first start SSMS and from the main menu, select the Tools menu, and click the Code Snippets Manager option:

This will open the Code Snippets Manager window:

Clicking the Add button, will open the Code Snippets Directory window, locate the folder that contains a SQL snippets and click the Select Folder button:

After clicking the Select Folder button, the folder MyNewSnippets will appear in the Code Snippets Manager list with all SQL snippets in it:

Importing a SQL snippet

To import individual SQL snippets, open SSMS, from the Tools menu, click the Code Snippets Manager option and from the Code Snippets Manager window choose the Import button:

This will open the Code Snippets Directory window, locate the folder that contains snippets, select one or more the. snippet files, and click the Open button:

This will open the Import Code Snippet window in which can be chosen location for the snippet that imported. By default, the My Code Snippets folder is checked, but you can choose any user added folder from the list to import a snippet:

Click the Finish button, will import a SQL snippet:

By default the My Code Snippets folder is located under this path: C:\Users\<UserName>\Documents\SQL Server Management Studio\Code Snippets\SQL\

Importing custom SQL snippets in the query window is the same as importing defaults SQL snippets.

Creating a Surround SQL snippet

The principle of creating a Surround SQL snippet is the same as creating a custom snippet but with minor changes.

In the Header section, the SnippetType XML tag is required, which must have a value “SurroundsWith”:

<SnippetType> SurroundsWith</SnippetType>

SurroundsWith allows the code snippet to be placed around a selected code in the query window.

In the Header section under the SnippetType place the SurroundsWith value and in the Code section should be added a $selected$ mark like in the example for the BEGIN END block below:

$selected$ is a place holder for the code selected in the query window that will be inserted into the snippet when invoked.

The steps for registering a surround SQL snippet in the SSMS is the same as for the custom SQL snippet.

Marko Zivkovic

Marko aka “Zivko” is a senior software analyst from Nis, Serbia focusing on SQL Server and MySQL as well as client technologies like SSMS, Visual Studio, and VSCode. He has extensive experience with quality assurance, issue escalation/resolution, and product evangelism.

He is a prolific author of authoritative content related to SQL Server including a number of “platinum” articles (top 1% in terms of popularity and engagement). His writing covers a range of topics on MySQL and SQL Server including remote/linked servers, import/export, LocalDB, SSMS, and more.

In his part-time, Zivko likes basketball, foosball (table-soccer), and rock music.

See more about Marko at LinkedIn

View all posts by Marko Zivkovic

Latest posts by Marko Zivkovic (see all)

How to connect to a remote MySQL server using SSL on Ubuntu - April 28, 2020
How to install MySQL on Ubuntu - March 10, 2020
Using SSH keys to connect to a remote MySQL Server - November 28, 2019