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):
- 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.
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:
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.