Code Snippets in SQL Server 2012 For Greater Team Productivity

Article
05/15/2013

 Included in this article:

What are snippets?
Overview
Configuration
How to use snippets
How to create snippets
Example Snippet - Find tables using Sequences

WHAT ARE SNIPPETS?

SQL Server Code Snippets are what they sound like - they are snippets of code that may be accessed easily through a program such as SQL Server Management Studio or Visual Studio Team System. For the purposes of this article, we are focussing on using them in SQL Server Management Studio, in the 2012 edition.

OVERVIEW:

In my team, we use SQL Code Snippets in SQL Server 2012 to save time for authoring queries that may be reused many times in our development cycle. This post is to detail how to use Snippets in SQL 2012, and a simple means to share the snippets among the team. We gain a lot of productivity by sharing our snippets through a source control product, TFS (Team Foundation Server), and retrieving through VSTS (Visual Studio Team System) to a local hard drive. This provides a very simple procedure to share snippets across the team as well as the normal benefits of a source control product.

In my experience, I've used Insert Snippets and Surrounds With Snippets. I favor Insert Snippets whenever possible. You can still get the benefits of highlighting text that should be modified with Insert Snippets, the only practical difference is that you type your changes after you implement an Insert Snippet, and you type your changes before you implement a Surrounds With Snippet. This is definitely a personal opinion, so I encourage you to try both. I lean towards one or the other because if you have a large library of snippets, and hopefully you chose to arrange them in hierarchical folders, it can be difficult to remember if you need to navigate one way or the other.

In the example given later in this article, I will create an Insert Snippet that helps identify tables in your database which implement Sequences. Parameters given include SchemaName, TableName, and ColumnName. These parameters will act as filters, and only the Schema Name is required. For more practical information on SQL Server Sequences, please refer to my earlier blog post. https://blogs.msdn.com/b/kenobonn/archive/2013/05/14/sql-server-2012-sequences-in-vsts-projects.aspx

We have two main hierarchies of user defined snippets. One is project specific, another is SQL specific. The project specific node has subnodes for features of the project, and can also be arranged by developer code versus troubleshooting code, where you may consider test and production support as users who may benefit as well. The SQL specific node is mainly for SQL tricks or common code that you may run in development or production. The root node of my Snippet library begins with a period, to place it at the top, alphabetically, as this is the most common set of snippets that I use.

CONFIGURATION:

Assumption is that SQL Server 2012 Client Tools are installed, and Visual Studio (2008 or above) with Team features to integrate with a TFS project.

First, make a note of the location in TFS Source Control Explorer where the SQL Snippets are stored. See Figure 1 below.
1. Write down the Local Path that maps to the root of the Snippets directory.
2. In the example below, the location is “C:\tfs\Tools\Team Standards\.Snippets”.
In SQL Server Management Studio (SSMS), go to "Tools", "Code Snippets Manager...". See Figure 2 below.
Add a reference to the directory where Snippets from TFS are located. See Figures 3 and 4 below.
In Visual Studio 2010, bring up the Code Snippets Manager by using the keys CTRL-K, CTRL-B (while holding down the CTRL key, press the K then the B keys). Change the language to SQL, and Add the directory for code snippets as identified earlier in step 1 b.

FIGURE 1: VSTS Source Control Mapping

FIGURE 2: Code Snippets Manager in SSMS - menu

FIGURE 3: Code Snippets Manager in SSMS – Add new directory

FIGURE 4: Mapping SSMS Snippets to local hard drive

HOW TO USE SQL SNIPPETS
To use SQL 2012 snippets, there are two kinds of snippets, Insert and Surrounds With.

For Insert, simply right click in query window, choose "Insert snippet..." and navigate the hierarchy of folders to the snippet of interest. See Figure 5 for an example of an Insert Snippet.
For Surrounds with, type a value or SQL statement and select it, so that it is the focus of the query window. Right click in the query window, and choose “Surround with…”. See Figure 6 for an example of a Surrounds With snippet.

FIGURE 5: How to use an Insert snippet

FIGURE 6: How to use a Surrounds With snippet

HOW TO CREATE SQL SNIPPETS

Templates for creating SQL Snippets are stored in the Snippets root directory. Included below are the templates for Insert and Surrouds With snippets. To create a new snippet, edit the template of interest by opening the file in Visual Studio. Store the changes in the appropriate directory, and save to source control. See the References section for help in creating snippets.

INSERT SNIPPET TEMPLATE
For an Insert Snippet, use the template below and modify the Title, Description, Author, and the code within the CDATA section below. The Title is what the user will click to select the snippet, the Description is hover text to help the user understand the purpose of the snippet. There may be zero or many Literals within the Declarations element. A Literal is something that will be used in substition in the resultant code, and is referenced by its name surrounded by $ symbols.

 <?xml version="1.0" encoding="utf-8" ?>
 <CodeSnippets xmlns="https://schemas.microsoft.com/VisualStudio/2005/CodeSnippet">
 <CodeSnippet Format="1.0.0">
 <Header>
 <Title>Template - Insert</Title>
 <Description>Demo for Insert Code Snippet.</Description>
 <Author> Ken O. Bonn </Author>
 <SnippetTypes>
 <SnippetType>Expansion</SnippetType>
 </SnippetTypes>
 </Header>
 <Snippet>
 <Declarations>
 <Literal>
 <ID>DatabaseName</ID>
 <ToolTip>Name of database.</ToolTip>
 <Default></Default>
 </Literal>
 </Declarations>
 <Code Language="SQL">
 <![CDATA[
 ------------------------------------------------------
 -- FILENAME: Template-Expansion.snippet
 -- PURPOSE: Template for Expansion (Insert) snippets.
 -- AUTHOR: Ken O. Bonn
 -- DATE: May 15, 2013
 ------------------------------------------------------
 SET NOCOUNT ON;
 
 EXEC SP_HELPDB $DatabaseName$;
 
 ]]>
 </Code>
 </Snippet>
 </CodeSnippet>
 </CodeSnippets>

SURROUNDS WITH SNIPPET TEMPLATE

The MSDN documentation has a nice example of a Surrounds With Snippet that you can use as a template. As I do not favor these snippets, I will refer to the link below in case you wish to create a Surrounds With snippet.

https://msdn.microsoft.com/en-us/library/gg492130.aspx

EXAMPLE - FIND TABLES USING SEQUENCES FOR DEFAULT VALUES

Copy and paste the code below into a directory that you configured for Code Snippets. Afterwards, follow the procedures that I detailed for calling an Insert Snippet. If you don't have a Sequence in your database, you may want to do the exercises in my earlier article, at https://blogs.msdn.com/b/kenobonn/archive/2013/05/14/sql-server-2012-sequences-in-vsts-projects.aspx

 <?xml version="1.0" encoding="utf-8" ?>
 <CodeSnippets xmlns="https://schemas.microsoft.com/VisualStudio/2005/CodeSnippet">
 <CodeSnippet Format="1.0.0">
 <Header>
 <Title>Columns using Sequences</Title>
 <Description>Find Columns that use Sequences as default values.</Description>
 <Author> Ken O. Bonn </Author>
 <SnippetTypes>
 <SnippetType>Expansion</SnippetType>
 </SnippetTypes>
 </Header>
 <Snippet>
 <Declarations>
 <Literal>
 <ID>SchemaName</ID>
 <ToolTip>Schema Name to find Sequence. Required.</ToolTip>
 <Default>dbo</Default>
 </Literal>
 <Literal>
 <ID>TableName</ID>
 <ToolTip>Table Name to find Sequence. Optional.</ToolTip>
 <Default></Default>
 </Literal>
 <Literal>
 <ID>ColumnName</ID>
 <ToolTip>Column Name to find Sequence. Optional.</ToolTip>
 <Default></Default>
 </Literal>
 </Declarations>
 <Code Language="SQL">
 <![CDATA[
 ---------------------------------------------------------------------
 -- FILENAME: ColumnsUsingSequences.snippet
 -- PURPOSE: Find tables having columns that implement Sequences.
 -- AUTHOR: Ken O. Bonn
 -- DATE: January 24, 2013
 ---------------------------------------------------------------------
 SET NOCOUNT ON;
 
 DECLARE @SchemaName sysname
 , @TableName sysname
 , @ColumnName sysname;
 SET @SchemaName = '$SchemaName$';
 SET @TableName = '$TableName$';
 SET @ColumnName = '$ColumnName$';
 
 SELECT
 [Schema Name] = scc.[name]
 , [Table Name] = so.[name]
 , [Column Name] = sc.[name]
 , [Constraint Name] = dc.[name]
 , [Sequence Name] = LTRIM(REPLACE(SUBSTRING(dc.[definition],
 CHARINDEX(dc.[definition], 'NEXT VALUE FOR') + 16, 100
 ), ')', ''))
 , [Sequence Object ID] = OBJECT_ID(LTRIM(REPLACE(SUBSTRING(dc.[definition],
 CHARINDEX(dc.[definition], 'NEXT VALUE FOR') + 16, 100
 ), ')', '')))
 FROM sys.schemas scc
 INNER JOIN sys.objects so
 ON scc.[schema_id] = so.[schema_id]
 INNER JOIN sys.columns sc
 ON so.[object_id] = sc.[object_id]
 INNER JOIN sys.default_constraints dc
 ON so.object_id = dc.parent_object_id
 AND sc.[object_id] = dc.parent_object_id
 AND sc.column_id = dc.parent_column_id
 WHERE dc.[type] = 'D'
 AND dc.[definition] LIKE '%NEXT VALUE FOR%'
 AND (scc.[name] = @SchemaName OR RTRIM(@SchemaName) = '')
 AND (so.[name] = @TableName OR RTRIM(@TableName) = '')
 AND (sc.[name] = @ColumnName OR RTRIM(@ColumnName) = '')
 ORDER BY scc.[name], so.[name], sc.[name];
 ]]>
 </Code>
 </Snippet>
 </CodeSnippet>
 </CodeSnippets>

Code Snippets in SQL Server 2012 For Greater Team Productivity

Additional resources