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

AddFolderToSnippetManager

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

AddFolderToSnippetManager2

 

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:

 

2017-05-23 17_08_42-_C__Program Files (x86)_Microsoft SQL Server_130_Tools_Binn_ManagementStudio_SQL

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:

StoredProcinTree
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

2017-05-23 17_30_16-AD Hoc Scripts

Now this Snippet should be available in management Studio

 

1.Open a new query window and right click

2.Goto Insert Snippet

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

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

BackupDatabaseCode

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:

2017-05-23 17_40_36-SQLQuery9.sql - LAPTOP-ADRIANB_SQLDEVEDITION.SQLUndercover (ADASTRA_buckmana (64

And outputs:

2017-05-23 17_41_24-SQLQuery9.sql - LAPTOP-ADRIANB_SQLDEVEDITION.SQLUndercover (ADASTRA_buckmana (64

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.

2 thoughts on “Snippets – A Couple of clicks to reduce repetitive typing.

Add yours

  1. This is really cool — few people know that there’s native support for this kind of thing, so they turn to RedGate SQL Prompt or other add-ins for snippet-management. Great post!

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Create a website or blog at WordPress.com

Up ↑

%d bloggers like this: