Skip to main content
Dave Mason - Mastodon Dave Mason - LinkedIn Dave Mason - GitHub Dave Mason - Counter Social

Adding Custom Transact-SQL Code Snippets

Dave Mason - SQL Server Management Studio

I have a number of scripts and queries I've written and curated over the years. They're not organized that well, scattered across different folders. Some are named poorly or grouped in a questionable manner. There are a handful that I tend to use the most. And yet with that small number, I sometimes have difficulty quickly finding a particular script (if I can find it at all), or spending too many mouse clicks to find it. It dawned on me recently to make use of code snippets.

Code snippets may have been intended primarily to aid code writing, but they can assist with administrative tasks too. Regardless of purpose, SSMS supports creating your own snippets, and we can create our first snippet in three steps:

  1. Copy an existing code snippet file.
  2. Edit it.
  3. Import it into SSMS.

Step 1

To get started, open the Code Snippets Manager from within SSMS via the Tools menu.

Dave Mason - SSMS Code Snippets Manager

Note the location where the built-in "Function" snippets are located, and then navigate to that path in Windows Explorer (you can highlight the path with your mouse and copy it, if desired).

Dave Mason - Code Snippets Manager

Find the "Begin End.snippet" file, and copy it. Create a "My Snippets" folder in your "My Documents" folder, paste the file into it, and rename the file to "Last Restart.snippet".

Step 2

Next, open the file in a text editor. I use Visual Studio 2019, but any editor will do. You'll probably notice the snippet file is comprised of XML data. Here's what it looks like:

<?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>Begin</Title>
      <Shortcut></Shortcut>
      <Description>Code Snippet for Begin...End block.</Description>
      <Author>Microsoft Corporation</Author>
      <SnippetTypes>
        <SnippetType>SurroundsWith</SnippetType>
      </SnippetTypes>
    </Header>
    <Snippet>
      <Declarations></Declarations>
      <Code Language="SQL">
        <![CDATA[
BEGIN

$selected$ $end$

END
]]>
      </Code>
    </Snippet>
  </CodeSnippet>
</CodeSnippets>

Now we'll edit the file to create a "Last Restart" code snippet that inserts this query to an SSMS code tab/window:

SELECT i.sqlserver_start_time
FROM sys.dm_os_sys_info i;

The values for the Title, Description, and Author nodes (highlighted in yellow) become "Last Restart", "Query that returns the date and time SQL was last started.", and "Dave Mason @BeginTry" respectively. The SnippetTypes node (highlighted in orange) is not needed--delete it. The code for our snippet query (also highlighted in yellow) goes in the Code node, within the CDATA section. Here is the end result:

<?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>Last Restart</Title>
      <Shortcut></Shortcut>
      <Description>Query that returns the date and time SQL was last started.</Description>
      <Author>Dave Mason @BeginTry</Author>
    </Header>
    <Snippet>
      <Code Language="SQL">
        <![CDATA[SELECT i.sqlserver_start_time
FROM sys.dm_os_sys_info i;]]>
      </Code>
    </Snippet>
  </CodeSnippet>
</CodeSnippets>

Step 3

Lastly, we'll import the code snippet to SSMS, so return to Code Snippets Manager. I had some trouble importing *.snippet files into the default "My Code Snippets" category/folder (perhaps a folder permission issue?). I removed "My Code Snippets", clicked the "Add.." button, navigated to and selected the "My Snippets" folder I had previously created within "My Documents". Expanding "My Snippets" reveals the new snippet that was created.

Dave Mason - Code Snippets Manager

Using The New Snippet

Click OK to close Code Snippets Manager (if it's still open) and open a new SSMS code tab/window.

  1. Right-click anywhere and select "Insert Snippet...".
  2. A drop-down list of snippet categories appears. Double-click the "My snippets" category folder.
  3. The "Last Restart" code snippet will be available (note the Tooltip). Double-click "Last Restart".
  4. The query from our snippet appears in the SSMS window.

Dave Mason - SSMS Insert Snippet
Dave Mason - SSMS Snippet Categories
Dave Mason - Last SQL Server Restart
Dave Mason - Last SQL Server Restart

Additional Snippets

If you want to add more snippets to SSMS, create a new *.snippet file, edit it, and copy it into the existing folder with "Last Restart.snippet". If the new snippet adheres to the Code Snippets Schema, it will show up automatically in SSMS.

I've created some other snippets that are available in this Github repository: BeginTry/SSMS-Code-Snippets. Many of them include replacement points, which will be of interest. Happy coding, everyone.

Comments

Comment by Victor Santos

Muchas gracias Dave, es magica esta herramienta, custom code snippets.

Victor Santos
Post comment