Snippets – A Couple of clicks to reduce repetitive typing.

I like finding ways of making things easier , recently I started toying with Snippets within SQL Server Management Studio to find that I could reduce the amount of repetitive typing that i regularly do for things such as calling store procedures.

I will show you what I did, there may be other ways and different possibilities but this particular one I found useful and I wanted to share.
To keep things simple I will use the default directory for Snippets to create my new folder but you can use what you like.
The default location can be found by opening the Code Snippets Manager.

1.Tools > Code Snippets Manager
2.Navigate to this file location and create a folder

2.Click Add and select your newly created folder, this will add the folder to the Snippets Manager list

 

Now we can add some snippets…

In this example I will use Stored procedures only as these are the ones that I found useful but you could experiment with the Surround With option if you have a need for this.
Below is a sample Snippet file for which we will work with:

<?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>
<_locTag _loc="locData">Default</_locTag>
</_locDefinition>
<CodeSnippet Format="1.0.0">
<Header>
<Title>Name of the Snippet (this will appear in the menu)</Title>
<Shortcut></Shortcut>
<Description>Enter a Description here this will show in the tooltip</Description>
<Author>Author name</Author>
<SnippetTypes>
<SnippetType>Expansion</SnippetType>
</SnippetTypes>
</Header>
<Snippet>
<Declarations>
<Literal>
<ID>Condition</ID>
<ToolTip>Condition to evaluate</ToolTip>
<Default>Condition</Default>
</Literal>
</Declarations>
<Code Language="SQL"><![CDATA[
<Enter your code here>
]]>
</Code>
</Snippet>
</CodeSnippet>
</CodeSnippets>

 

 

We are only interested in Altering the Highlighted parts below:

 

So I will use an Example Stored procedure that I have which I use frequently but and bored typing out the entire syntax every time.

Stored Procedure can be seen below:

I amend the Highlighted sections as follows:

FROM: <Title>Name of the Snippet (this will appear in the menu)</Title>
TO: <Title>BackupDatabase</Title>

FROM: <Description>Enter a Description here this will show in the tooltip</Description>
TO: <Description>Backup Database Stored Procedure</Description>

FROM: <Author>Author name</Author>
TO: <Author>Ade</Author>

FROM: <Code Language=”SQL”><![CDATA[
<Enter your code here>
]]>
</Code>

TO: <Code Language=”SQL”><![CDATA[
EXEC [SQLUndercover].[dbo].[BackupDatabase]
@Databasename = ‘SQLUndercover’,
@BackupType = ‘FULL’,
@AvailabilityGroup = ‘AG1’
]]>
</Code>

Save the File as a new name within my new folder ensuring that the extension stays as .snippet

Now this Snippet should be available in management Studio

 

1.Open a new query window and right click

2.Goto Insert Snippet

3.Click on your folder and you should see your newly created snippet

4.Click it to paste the contents directly into the Query window!

How easy was that! now you can reuse this code whenever you like without the need of Typing it all out or Opening an external document to Select all then copy and paste.

Here is another example which includes some comments:

<?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>
<_locTag _loc="locData">Default</_locTag>
</_locDefinition>
<CodeSnippet Format="1.0.0">
<Header>
<Title>MaintenanceReport</Title>
<Shortcut></Shortcut>
<Description>MaintenanceReport Stored Procedure</Description>
<Author>Ade</Author>
<SnippetTypes>
<SnippetType>Expansion</SnippetType>
</SnippetTypes>
</Header>
<Snippet>
<Declarations>
<Literal>
<ID>Condition</ID>
<ToolTip>Condition to evaluate</ToolTip>
<Default>Condition</Default>
</Literal>
</Declarations>
<Code Language="SQL"><![CDATA[
EXEC [SQLUnderCover].[dbo].[MaintenanceReport]
@DatabaseType = 'User', -- Include USER Databases only or ALL Databases
@SummaryOnly = 1, -- Summary Only = 1 , Will reduce the columns returned will not include large text columns in the results
@IndexCheck = 1, -- Check Indexes?
@BackupCheck = 1, -- Check Backups?
@AgentJobCheck = 1, -- Check Agent Jobs?
@FailedLoginAttempts = 1, -- Check Failed Login Attempts?
@DateFrom = '20170523 00:00' -- Date to start reporting from, cannot be NULL
]]>
</Code>
</Snippet>
</CodeSnippet>
</CodeSnippets>

 

Save file as MaintenanceReport.snippet

Now when i Right click in management studio and insert snippet I have another snippet available:

And outputs:

This one is great because I can just double click this whenever I need it and it will include the comments too which is brilliant for those procedures that you use once in a while and cannot remember ALL of the Variables available.

Thanks for reading.