Deeper into the SQL Server Management Studio Template Explorer

Open vs. Edit

When navigating through the various folders and templates in the Template Explorer, you have two options when it comes to displaying any given template: you can double-click the template and open it, or you can right-click and select either Open or Edit from the pop-up menu displayed.

Part One: Introduction to Using the Template Explorer in SQL Server Management Studio

There is an important distinction between double-clicking or selecting Open and selecting Edit. 

Opening the template will expose a new query and populate that query with the contents of the template file. Making changes to that query's code will not affect the existing template in any fashion.

However, if you select Edit that's a different story all together. In that case you're opening the template file and making changes. Any changes you make to that template when editing will be saved for future use. Use Edit with care.

Practical Use of Templates

If you're anything like me, you don't tend to like to do things more that once. That's a fairly bad trait for someone in tech. This means that if I end up either building a script to perform a repetitive task or find myself running an existing script more than a couple times, I'll take the one-time hit to swap out portion of the code that may change upon each use (such as database name, schedule perhaps if this is a creation of a SQL Agent job or perhaps a password because you'd never want that kind of information stored in a script) with the template parameter syntax I discussed in part one of this article:

< PARAMETER_NAME, PARAMETER DESCRIPTION, DEFAULT VALUE >

Once that's done, I create a new template in a custom folder in the Template Explorer and then copy and paste the code from my revised script into the new template and save it for future use.

Things to Keep in Mind

When swapping out values for template parameters, be sure to keep in mind that everything between the less-than and greater-than symbols will be swapped out. This means that if you're ultimately replacing a string, you'll want to have the parameter template code inserted between single quotes:

'< PARAMETER_NAME, PARAMETER DESCRIPTION, DEFAULT VALUE >'

Likewise, when parameterizing an object name, you'll typically want to have the parameter template code inserted between square brackets:

[< PARAMETER_NAME, PARAMETER DESCRIPTION, DEFAULT VALUE >]

Once you've parameterized the script to use as a template, you won't be able to successfully parse the command(s) so keep that under consideration as well.

Related: Simple SQL Server Management Studio Treck: Multiple Line Text Edition