If you work with SQL Server on a daily basis, it is very likely you have a lot of custom scripts you have to execute frequently, maybe you have stored them on a folder and you open them manually as you need them, or have saved them on a solution or project file, maybe you execute a custom .bat or PowerShell file to load them when you open SSMS...

Every method has its pros and cons, and on this post, I will show you a new method to load your custom scripts on any open query window on SSMS via Snippets.

What is a Snippet?

According to Microsoft Documentation:

Code snippets are small blocks of reusable code that can be inserted in a code file using a context menu command or a combination of hotkeys. They typically contain commonly-used code blocks such as try-finally or if-else blocks, but they can be used to insert entire classes or methods.

In short words is custom code (T-SQL Scripts for us) that you use a lot and you want to have always available to use.
In SSMS there are a lot of code snippets available by default, that you can use right away, let us see one of them in action, we will use a snippet to insert a create table template:

You can see how easy is to load the code in any existing window, even if this already has code on it, so now let us create one with our custom code.

Creating a custom Snippet

These files are stored in XML format with a .snippet extension, you can find the official documentation here.

The basic template is this (you can find the file on my GitHub):

<?xml version="1.0" encoding="utf-8" ?>
<CodeSnippets xmlns="http://schemas.microsoft.com/VisualStudio/2005/CodeSnippet">
<CodeSnippet Format="1.0.0">
<Header>
<Title>Your Snippet title</Title>
<Shortcut></Shortcut>
<Description>Your snippet description</Description>
<Author>Author of the snippet</Author>
<SnippetTypes>
<SnippetType>Expansion</SnippetType>
</SnippetTypes>
</Header>
<Snippet>
<Declarations>
<Literal>
<ID>Variable1</ID>
<ToolTip>Variable1 description</ToolTip>
<Default>variable1 default value</Default>
</Literal>
</Declarations>
<Code Language="SQL">
<![CDATA[
SELECT $Variable1$
]]>
</Code>
</Snippet>
</CodeSnippet>
</CodeSnippets>

Explaining it:

• You need to insert your custom code inside the <![CDATA[]]> label. 
• If you have variables, you must enclose them in $ characters ($Variable1$ for our template, and then declaring the variable with the name you use inside the <Declarations> section.
• Then declare the Script name, description, and author in the <Header> section.
• Save it with the .snippet extension.

I have a simple script that I use a lot to see the elapsed and remaining time for any session on SQL server, so let us use it for this example, the query is the following (Note: I didn't make this script, but I was not able to find the author, so if you know him/her, please let me know so I can give the proper credit):

SELECT 
 r.session_id,
 r.command,
CONVERT(NUMERIC(6,2),r.percent_complete) AS [Percent Complete],
CONVERT(VARCHAR(20),DATEADD(ms,r.estimated_completion_time,GetDate()),20) AS [ETA Completion Time],
CONVERT(NUMERIC(10,2),r.total_elapsed_time/1000.0/60.0) AS [Elapsed Min],
CONVERT(NUMERIC(10,2),r.estimated_completion_time/1000.0/60.0) AS [ETA Min],
CONVERT(NUMERIC(10,2),r.estimated_completion_time/1000.0/60.0/60.0) AS [ETA Hours],
CONVERT(VARCHAR(1000),(SELECT SUBSTRING(text,r.statement_start_offset/2,
CASE WHEN r.statement_end_offset = -1 
THEN 1000 ELSE (r.statement_end_offset-r.statement_start_offset)/2 END)
FROM sys.dm_exec_sql_text(sql_handle)))AS [SQL Text]
FROM sys.dm_exec_requests r WHERE command LIKE  '%%' --Text of the Session you want to monitor, like BACKUP or DBCC

For this script, our variable will be the text in the like statement, so after using the snippet template, our code should look like this:

<?xml version="1.0" encoding="utf-8" ?>
<CodeSnippets xmlns="http://schemas.microsoft.com/VisualStudio/2005/CodeSnippet">
<CodeSnippet Format="1.0.0">
<Header>
<Title>Running Queries ETA</Title>
<Shortcut></Shortcut>
<Description>Display estimated completed time for any given query</Description>
<Author>SQLGuatemala.com</Author>
<SnippetTypes>
<SnippetType>Expansion</SnippetType>
</SnippetTypes>
</Header>
<Snippet>
<Declarations>
<Literal>
<ID>YourCommand</ID>
<ToolTip>Command to look for</ToolTip>
<Default>BACKUP</Default>
</Literal>
</Declarations>
<Code Language="SQL">
<![CDATA[
SELECT 
 r.session_id,
 r.command,
 CONVERT(NUMERIC(6,2),r.percent_complete) AS [Percent Complete],
 CONVERT(VARCHAR(20),DATEADD(ms,r.estimated_completion_time,GetDate()),20) AS [ETA Completion Time],
 CONVERT(NUMERIC(10,2),r.total_elapsed_time/1000.0/60.0) AS [Elapsed Min],
 CONVERT(NUMERIC(10,2),r.estimated_completion_time/1000.0/60.0) AS [ETA Min],
 CONVERT(NUMERIC(10,2),r.estimated_completion_time/1000.0/60.0/60.0) AS [ETA Hours],
 CONVERT(VARCHAR(1000),(SELECT SUBSTRING(text,r.statement_start_offset/2,
 CASE WHEN r.statement_end_offset = -1 
 THEN 1000 ELSE (r.statement_end_offset-r.statement_start_offset)/2 END)
FROM sys.dm_exec_sql_text(sql_handle)))AS [SQL Text]
FROM sys.dm_exec_requests r WHERE command LIKE  '%$YourCommand$%'
]]>
</Code>
</Snippet>
</CodeSnippet>
</CodeSnippets>

We save it with the .snippet extension to a folder in our computer, the next step is to load it to SSMS.

Loading the Snippet into SSMS

Open SSMS and go to Tools> Code Snippets Manager

The following window will open. If you want to load a complete snippet folder, click on Add, or if you want to load just one snippet, click on Import.

For this example we will load the entire folder where we store the snippet template and the one we created in the previous step, so we click on Add, and then locate the snippet folder:

We can see that the custom Snippets folder has been created and the 2 snippets are loaded.

After that, we can use the custom code snippets on any window we want.

Loading our Custom Snippet

Now you can load your custom code in any SSMS open query window, we will use it to know how much time is taking a DBCC command we are running on the other window:

For more information on T-SQL snippets, visit this link.