I Thought Everyone Did It Like That – SSMS TemplatesPublished 12 December 2013 7:00 am
One of an occasional series of blogs about things that I do that perhaps others don’t.
SSMS – the default TSQL code editor that comes with SQL Server has many faults and my Twitter stream is often the scene of DBAs and developers venting their frustrations at it’s foibles but it does have some quite useful features. One of those that I am coming to understand that a lot of people don’t use is Templates. Lets take a look at them and how they can be of use to a SQL Server professional that is in a hurry (and lets face it, we are all pretty busy!).
Templates? What are you talking about?
By default when you open up SQL Server Management Studio (SSMS) the Template Explorer isn’t visible. It might be hidden behind another tab or it may actually be closed. Press Ctrl + Alt + T or use the View menu to bring it into view.
In general the Template Explorer will sit, docked beside the Solution Explorer. If you want to view it more easily you can drag it away from there so you can take a look at what it contains without content scrolling out of view. Click and drag it by its Title Bar into the middle of the SSMS window. Once you are done with it, double click the Title Bar and it will re-dock itself back in the side of your SSMS editor. For the purposes of this blog I’m just going to resize the view of it while docked by dragging the edges of the window.
Template Explorer is a lot like Windows Explorer and in fact it is simply giving us a view of a folder structure inside the SSMS installation as we can see if we navigate to C:\Program Files (x86)\Microsoft SQL Server\110\Tools\Binn\ManagementStudio\SqlWorkbenchProjectItems\Sql where we see some the exact same folders as we do in the Template Explorer.
You will note that this folder location is version specific, in so far as the folder path contains ‘… SQL Server Management Studio\11.0\Templates …’ directory where the 11.0 is created by a SQL Server 2012 installation. This means that you can have a different set of templates available in each version of SQL Server tools that you install on your computer. From Microsoft’s point of view this makes total sense as syntax changes that each version needs mean that the template code may well alter between versions. It doesn’t however stop you opening a template in SSMS 2012 that is not compatible with the server connection that you have active. Please keep this in mind when using templates if you get errors of this sort.
Got it. How do they work?
Let’s jump right in and open the Backup Database template simply by double clicking it in the Template Explorer. SSMS will open a new Text Editor window for you and paste code into it, similar to that shown below.
You will probably notice that there are a few areas that SSMS has underlined with wavy red lines indicting syntax errors. Don’t worry about those for the moment but take a good look at the code that is in the editor and you will see that there are sections of code bounded by < and > characters ( I have highlighted one above). These are SSMS parameter place holders. Notice that they all have the same but of the four that are on screen 3 are identical and one is different. They all share the fact that there are 3 sections between the < and >, separated by two commas. Press Ctrl + Shift + M.
You will get a dialog box as shown below with columns that match the sections of the parameter values – Parameter, Type and Value. You can see below that no Type is specified in the Backup_Path parameter but sysname is in the Database_Name one. As there are only two distinct parameters in the TSQL Editor the UI gives us two parameters to work with:
You can type directly into the Value column for each parameter and once you have entered all that you want to click the OK button.
SSMS will take your input and for each parameter in the code in the active window it will replace the whole string between < and > with the value that you supplied, if the parameter is repeated a number of times as in our example then the value gets used all of those times.
And here we have a piece of code that is ready to run.
Now, we go this code from a template but you can actually use the <ParameterName, Type, Value> construct in any code that you create and replace it in this manner whenever you reuse it.
This use of templates is pretty useful, take a look at the number and variety of templates there are out of the box. Can’t remember the code to create a Service Broker Queue or to create a Certificate? No problem, dive into the templates library and get it into action in seconds.
Keep in mind that when you double click this template you are not opening the .sql file we saw in the folder location above, SSMS is taking the contents of that file and pasting it into a new query editor window. Any changes you make to the code don’t affect the template. If you want to save the code that you create then you simply hit Save and fix the new file into a location of your choosing. Next time you go to the template it will be identical to how you found it the very first time.
That’s pretty neat but seems a bit limited.
Well, I can see what you are saying but don’t underestimate how useful this can be, it’s code that is available to you regardless of which server you have connected SSMS to so it’s no reliant on any code being installed on the remote instance.
Let’s take this a bit further though, we can add our own favourite pieces of code so that they are available through the Template Explorer. First we can look at a way to add new templates one at a time, as and when we come across code that we want to make us of as a template. Right-click on the SQL Server Templates node of the Explorer and choose New | Folder
This creates a new folder in the Template Explorer at the bottom of the folder list, ready for you to name it:
Go ahead and name it and then, in just a moment we will right-click on it and choose New | Template, first of all, a bit like they do in magic tricks where you have to think of a number, you need to think of a file name for your template. Got one?
Great, now go ahead and right-click , New, Template and then type in your template name. For my example I am going to create a template for using sp_whoisactive* in the format that collects data to a table so I will be calling the template Who Is Active – Table.sql. Don’t forget the file type!
Now right-click on the new template name and choose Edit – don’t double click because, as we noted earlier, copies the template content to a new code window and we want to actually edit the template itself.
I’ve pasted the code I want to use into the SSMS editor and then replaced some sections of code with the SSMS parameter code blocks as show below.
Quickly check the parameter code blocks are well formed by pressing Ctrl + Shift + M and reviewing the popup dialog.
Cancel out of this dialog, save the template as you would any other query and then close the file.
All that is left is to locate the template in the Template Explorer and double-click it to test it works as expected.
Now, whenever you need the code you know it will load and you can easily adjust it to collect data to meet your needs at the time.
OK, I see but I don’t use sp_whoisactive very often. What is there that will be used more frequently?
Talk about demanding! Alright then, let see how we can add and use templates with code we use more frequently. Everyone uses a numbers table to help them produce a series of dates or items with no dates or to aid in some other way with their TSQL activities. Let’s create a new template file and call it digits.sql. Edit the template and paste this code into it:
DECLARE @digits TABLE ( d INT ) INSERT @digits ( [d] ) SELECT TOP 10 ROW_NUMBER() OVER ( ORDER BY id ) - 1 FROM [sys].[sysobjects] AS S
Save this template and create a new one, this time calling it Numbers.sql and paste this code into it:
/*** Table Variable ***/ DECLARE @Numbers TABLE ( N INT ) INSERT @Numbers ( [N] ) SELECT D.[d] + [D2].[d] * 10 + [D3].[d] * 100 + [D4].[d] * 1000 FROM @digits AS D , @digits AS D2 , @digits AS D3 , @digits AS D4 /*** Database table ***/ CREATE TABLE Numbers ( N INT ) INSERT Numbers ( [N] ) SELECT D.[d] + [D2].[d] * 10 + [D3].[d] * 100 + [D4].[d] * 1000 FROM @digits AS D , @digits AS D2 , @digits AS D3 , @digits AS D4
Again, save this and then close all code windows.
Now open a new, empty code window ready for some TSQL to be added. Locate your digits template and click and drag the template over onto the text editor. When you release the mouse button the code from the template will be pasted in to the editor. Move your cursor to the end of the code and add a new line of two at the bottom and then click and drag the numbers template into the editor window. The code from this template will be pasted underneath the existing ‘digits’ code.
When you let go this time you get the numbers code pasted in below the existing code. In fact, if you keep the mouse button pressed you can more the carat that indicates where the code will be pasted around the screen to the point that you want it inserted.
Create a template for each of the frequently used sections of code and you can quickly build up a library of code extracts that will speed up your production of reliable and consistent code.
Here we have taken a quick tour of the SSMS Templates facility and looked at how to create our own templates that will help us store and locate code that we want to reuse and need to access quickly. We have also taken a look at using Template Parameters to help us adapt the code from these templates to suit our varying needs at the time of use. What templates would you create – how about one for the pivot TSQL? or maybe one for generating a random number?
* – If you have never heard of sp_whoisactive then I wholly recommend you move right along to read about it and download the latest version from Adam Machanic’s website. It’s a vital tool to have and understand how to use that does an excellent job of illustrating what might be causing performance problems on a SQL Server that Adam very kindly makes available for free. Also take a look at Brent Ozar’s blog where he describes the theory and method behind using sp_whoisactive to log data to a table.