Introduction to Using the Template Explorer in SQL Server Management Studio

Where Did I Put That?

If you've been working with Microsoft SQL Server for any length of time, you've probably strewn about a collection of scripts you call upon on a regular basis across many folders, thumb drives, cloud-based folder sync applications, and floppy disks (some of us remember those). Saving re-usable scripts is meant to save time, right? How does the overhead of remembering where you kept that script for looking up index usage on a table ("I know I put it here somewhere!") fit into the time-saving process though? There has to be a better way to keep these scripts organized and at your fingertips.

Well, what if I told you that Microsoft already has that set up for you? Ladies and gentlemen I give you...

The Template Explorer

The Template Explorer is navigable from the main SSMS menu via the View submenu or by the key-stroke shortcut of Ctrl+Alt+T. It's a dockable window that allows you to make use of Microsoft-shipped scripts (templates) that may or may not include parameterization functionality for re-use. The parameterization syntax is quite simple. A template parameter is identified by a left-leading "<" symbol and a closing ">" symbol. There are three parts of the template parameter:

Let's take a look at an example straight from SSMS 2012:

These are the contents of the Template Explorer on my laptop. There are a couple of items there at the top that don't particularly look Microsoft-shipped. That's because those are of my own creation. . . but more about that at the end of the article.

By double-clicking on the Bring Database Online template it opens in a new query window using the current security context inside of SSMS—always be sure to check that context before proceeding! At this point, that is what you should see on your screen if you're playing along at home:

This is the humble contents of this particular template, and you can see the template parameter for the database name and its three parts (name, description—in this case it's expected data type, and a default value). Now comes the fun part. By either selecting Specify Values for Template Parameters from the Query sub-menu on the SSMS menu or using the key-stroke shortcut of Ctrl+Shift+M, you're presented with a modal window for entering in a value for the Database_Name parameter:

You'll see the default value is pre-populated in this window. I can then change that to the database name I wish to use, hit OK, and the script will be transformed to remove the parameter syntax, everything between the <>, and replaced with the name of the database I intend to use when executing the script. 

Customization

Earlier, I asked you to disregard those two entries at the top of the Template Explorer window. Well, those would happen to be my own script repositories inside the Template Explorer. If we didn't have an option for adding our own templates, this functionality would be quite limited after all, wouldn't it? The customization process is easy. Simply right click on the top-level folder and you have the option to add subfolders or individual templates. These templates and folders are created under your login's folder structure inside of C:usersloginAppDataRoamingMicrosoftSQL Server Management StudioversionTemplatesSql. Unfortunately, that value isn't customizable at this time. Please note that the folder structure is slightly different if you're still using SSMS 2008R2, or older. (But why would you want to be doing that?)

As a preference, I came up with the naming convention I did because the alphabetical sorting that occurs within the Template Explorer. I wanted my personal templates to always display at the top of the window. One thing I should mention when it comes to customization and sorting however: Any changes made to template names and sorting will not take effect until after SSMS is restarted. Therefore, if you create a template and then rename it, it will not re-sort during the same session.

Stay Tuned for More Template Tips

I'll be back to show you some of the other things you can do with templates when we next look at editing existing templates. See you soon!

Part Two: Deeper into the SQL Server Management Studio Template Explorer