Tag Archives: ssms

SQL Server Life Hacks – TSQL Tuesday #123

For this months TSQL Tuesday the theme is life hacks so I thought I would share a feature I use to simplify my work life which is snippets.

At my work we have a number of different scripts we use for to handle different tasks within our SQL Server databases as well as for normal administrative purposes. So to simplify my life I make use of the snippet feature in SQL Server Management Studio. SQL Server has many pre-defined snippets you can use and you access them by using the keyboard shortcut Ctrl+K+X or you can right-click and choose “Insert Snippet…” from the dialog.

Snippets are defined using xml and I like to start with a template like the example code from Microsoft. Below is an example that I created of a small query one might type often during a work day.

<?xml version="1.0" encoding="utf-8" ?>  
<CodeSnippets  xmlns="https://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>  
   <_locTag _loc="locData">Default</_locTag>  
</_locDefinition>  
<CodeSnippet Format="1.0.0">  
<Header>  
<Title>Check status for failures</Title>  
                        <Shortcut></Shortcut>  
<Description>Checks for process status in failed state.</Description>  
<Author>Russell Johnson</Author>  
<SnippetTypes>  
                                <SnippetType>Expansion</SnippetType>  
</SnippetTypes>  
</Header>  
<Snippet>  
<Declarations>  
                                <Literal>  
                                <ID>CheckStatus</ID>  
                                <ToolTip>Check the status table for failures.</ToolTip>  
                                <Default>CheckStatus</Default>  
                                </Literal>  
</Declarations>  
<Code Language="SQL"><![CDATA[  
SELECT ProcessId, ProcessName, ErrorMessage FROM ProcessStatus WHERE IsError = 1 AND ErrorMessage IS NOT NULL
GO
]]>  
</Code>  
</Snippet>  
</CodeSnippet>  
</CodeSnippets>

To make your own snippet you would modify the Title, Description and Author in the header section along with the ID, Tooltip and Default portions of the Declarations section. Finally you add whatever code you want between the brackets in the CDATA section. This code can be simple like I show above or have many lines. It is great for creating templates for code generation giving you consistent look and feel for things like header sections in stored procedures that detail who made the code, code execution examples and the purpose in comments. There is an option to include variables that can be changed after you select the snippet and tab between them and hitting enter when done. You also have the option to modify the existing templates that came with your SSMS installation. You will save your code with the extension of .snippet to a directory of your choice.

To add new the new templates you create you open the Code Snippets Manager by doing the Ctrl+K+B keyboard shortcut or choosing the option under the Tools menu. When in the Code Snippets Manager you can add the directory where you stored your custom snippets or you can add a location within the tool then import your snippet to that location by choosing the import option. Once you have done this you can open your editor and access it.

There is another snippet type I’m not as familiar with called a surround snippet which does what it sounds like it does and surrounds your selected text with your template.

While researching an issue I was having I found a good blog article by Artemakis Artemiou at sqshack.com that goes over the creation of both types of snippets including a tool to help with their creation I’ve not used before. How to create and manage T-SQL code snippets

I hope that this tool might might speed up your day. Take care til next time.