Today I am continuing from my introduction to SSMS Template Explorer.  Part 2 today is a quick look at creating your own templates.  I mentioned in the previous post that if you delete one of the Microsoft provided templates, it will be recreated when you next launch SSMS.  Another thing of note: if you make a change to any of the default templates, those changes will keep when you next launch SSMS.  In other words, changes do not get overwritten.  My personal preference is to make a new template and not editing the Microsoft supplied template even though any edits that you make stick in the template.

Create the template file

1. First right-click on the root node in Template Explorer labeled “SQL Server Templates”
2. Select New, and then Folder
3. Name your folder.  I generally start my folder names with a period so that they get sorted at the top of the list.  for example: “.Admin”
4. Once you create your folder, Right click on that folder name.
5. Select New, and then Template
6. Name your template whatever makes sense to you.

Edit the template file

Now that your template is created, the next logical step is to double-click it to open it.  If you do that, you will see a blank query open up in the main window.  This is not exactly what you want.  Instead, right-click on your template and then click Edit.

Now when the blank query opens in the main window, it should have the template name in the query tab at the top.  By going this route, when you save the query your script will get saved into the template file.

The first thing I usually do is start with a quick header.  I will put some general information about what the template is for, the source if I copied it from a blog post somewhere, and the shortcut key to specify values for the template because I can never seem to remember them.

--------------------------------
-- Template Header
-- It's here so I remember what I am using this piece of script for
-- Created by Matt Nelson, 9/15/2011
--
-- Press CTRL + SHIFT + M to fill in variables (because I can never remember the key combination)
--------------------------------

I can add my script now that my header is in place.  There is a special syntax that you can use to use the CTRL+SHIFT+M shortcut to fill in blanks.  I am going to steal Microsoft’s Backup Database template here to show you the syntax

BACKUP DATABASE sysname, Database_Name>
TO  DISK = N'sysname, Database_Name>.bak'
WITH
NOFORMAT,
COMPRESSION,
NOINIT,
NAME = N'sysname, Database_Name>-Full Database Backup',
SKIP,
STATS = 10;
GO

When you want to make a variable placeholder in your script you will put a 3 part variable enclosed between the greater than and less than symbols: <Parameter,Type,Value>  like: sysname, Database_Name> .

• Parameter is basically the variable name.
• Type is a placeholder for the of variable.  There are no constraints around the type like there are with a database table column.  I generally leave this blank.
• Value can also be blank, or you can make it a sample for yourself.

Once you get your script all set up, don’t forget to save it.  The next time you want to run it, all you have to do is double-click on it in Template Explorer.