Using SSMS Templates for Fun and Profit!

The "best practices" concept is truly a great thing, especially when applied to T-SQL code. But does your best practices process amount to (a) carefully writing down the things you know you should do, and then (b) not having time to do them? If so, my next few posts will describe an easy way to capture and re-use your T-SQL best practices, saving time, improving code, and reducing bugs. No, really.

In this post, I'll walk through how to use one of the least-appreciated features of SQL Server Management Studio: the "Template Library." Future posts will teach you how to leverage SSMS Templates to create a flexible, personal "toolbox" of your debugged, error-checked, optimized, commented, and otherwise perfected T-SQL code snippets. (Post hoc: You can now download my library of templates from http://sqlsoundings.blogspot.com/p/sql-server-templates.html )

What's a Template?

Templates are simply Notepad-editable text files that have an "sql" extension: "SQL scripts", in other words. What makes them special is that they contain zero or more parameters, and reside in certain "well-known" folders.

Ok, What's a Parameter?

This is one of those things that's easier to demonstrate than explain. Here's a very simple line of code with a parameter in it:

DROP TABLE <table_name, sysname, your_table_name>

As you can see, a parameter is just a parameter_name, a data_type, and a default_value, surrounded by angle brackets. Actually, it's even easier than that: the parameter_name can be made readable by using upper- and lower-case letters, spaces, and (most) punctuation; the data_type doesn't actually do anything; and the default_value is optional. So this is also a legal parameter:

DROP TABLE <Table Name,,>

Of course, including a default_value is usually a good idea, if only to jog your memory. Even better, since data_type doesn't do anything (I call this "meekly"-typed), we could conceivably use it for documentation:

DROP TABLE <Table Name, Must begin with "tbl"!, tbl>

So far, using Templates might look like more work than it's worth. And it might be, except... Microsoft has written a bunch of them for you!

So How do I Use a Template?

To see the available Microsoft-authored Templates, we use the "Template Explorer" window, via the "View, Template Explorer" menu item. The familiar tree-of-folders-and-files control appears, with the Templates arranged into folders based on database object type. For example, here's the Create Unique Nonclustered Index Template from the Index folder:

-- ===================================
-- Create Unique Nonclustered Index
-- ===================================
USE <database_name, sysname, AdventureWorks>
GO

CREATE UNIQUE NONCLUSTERED INDEX <index_name,sysname,AK_EmployeeAddress_rowguid> 
ON <schema_name,sysname,HumanResources>.<table_name,sysname,EmployeeAddress> 
(
 <column_name,sysname,rowguid> ASC
)
WITH 
(
 SORT_IN_TEMPDB = OFF, 
 DROP_EXISTING = OFF
) 
ON <file_group,,[PRIMARY]>
GO

EXEC sys.sp_addextendedproperty 
 @name=N'MS_Description', 
 @value=N'<description_index,string,Description of index>' ,
 @level0type=N'SCHEMA', 
 @level0name=N'<schema_name,sysname,HumanResources>', 
 @level1type=N'TABLE', 
 @level1name=N'<table_name,sysname,EmployeeAddress>', 
 @level2type=N'INDEX', 
 @level2name=N'<index_name,sysname,AK_EmployeeAddress_rowguid>'
GO

As you can see, there are several parameters in this Template; if there's a limit, I've never run into it. It's important to understand that the parameters describe simple text-replacement: they know nothing of T-SQL. This means they can be used to parameterize anything: database object names, text inside quotes, text in comments, portions of object names, fragments of T-SQL code, etc.

Let's see how you use this Template to create a unique nonclustered index. First, open a new (or existing) SQL file. Now drag-and-drop the Create Unique Nonclustered Index Template from the Index folder to the desired location in the editor window; this pastes the Template's contents at the drop location.

Now comes the fun part: press the Ctrl-Shift-M chord, or use the Query, Specify Values for Template Parameters menu item. This will cause SSMS to pop this dialog:

This dialog is what makes Templates so useful. There's one row for each parameter, and the columns are the variable name, data_type, and default_value for each one. To replace all the parameters in your SQL document, you enter values (or accept the defaults) for each row. (Naturally, the left and center columns in this dialog are read-only.) Once you're happy with the values you've entered, click the "OK" button, and they will be substituted for the Template parameters, resulting in T-SQL code like:

-- ===================================
-- Create Unique Nonclustered Index
-- ===================================
USE AdventureWorks
GO

CREATE UNIQUE NONCLUSTERED INDEX AK_EmployeeAddress_rowguid 
ON HumanResources.EmployeeAddress 
(
 rowguid ASC
)
WITH 
(
 SORT_IN_TEMPDB = OFF, 
 DROP_EXISTING = OFF
) 
ON [PRIMARY]
GO

EXEC sys.sp_addextendedproperty 
 @name=N'MS_Description', 
 @value=N'Supports the Employee Address Report for Marketing - see Pam for details' ,
 @level0type=N'SCHEMA', 
 @level0name=N'HumanResources', 
 @level1type=N'TABLE', 
 @level1name=N'EmployeeAddress', 
 @level2type=N'INDEX', 
 @level2name=N'AK_EmployeeAddress_rowguid'
GO

Some things to note about this dialog box:

• Each variable-name appears once, no matter how many times it's used in the Template's T-SQL code.
• Don't allow two parameters to have the same variable-name but different data-types, because it won't work: only the first parameter for a variable-name appears in the dialog.
• You can use the tab key to navigate, but it's faster to use the down arrow after entering each value.
• Drag-and-dropping copies the contents of the Template to the editor window.
• Double-clicking opens a new window, and copies the contents of the Template to the editor window. However, this is not how you edit a Template.
• To edit a Template, right-click on it, and select the "Edit" menu item.
• This is, for some reason, a modal dialog, so be sure to have whatever text you need available to you (on the clipboard, in an open text file, etc.) before popping it.
• Be sure not to use extra commas or angle brackets in a parameter, as this usually confuses the parser.

Where are the Templates?

This can be a little confusing because of the way SSMS behaves. There are two locations for the Microsoft-authored Templates: a "factory-installed" one for SSMS to use, and a user-specific one:

• The original, "factory-installed" copies can be found in C:\Program Files\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\SqlWorkbenchProjectItems\Sql for SQL Server 2008, or in C:\Program Files\Microsoft SQL Server\90\Tools\Binn\VSShell\Common7\IDE\SqlWorkbenchProjectItems\Sql for SQL Server 2005. The Templates in these folders cannot be changed via SSMS; they will always be preserved in their original state.
• The location of the local, user-specific copies depends on your version of SQL Server and your operating system. Using the %APPDATA% Windows environment variable (which makes it operating system independent), and knowing that SQL Server 2005 is "version 9", and "SQL Server 2008" is "version 10", we can represent the local Template folder's name compactly as:

  %APPDATA%\Microsoft\Microsoft SQL Server\{SQL Version}0\Tools\Shell\Templates\Sql
  

The difficult thing to remember (for me, at least) is that what you're seeing in the "Template Explorer" window is your local files, not the original ones. So, edits to the these Templates will be saved to your local Template folder.

Every time it runs, SSMS makes sure that each user has a copy of some kind of all the Microsoft-authored Templates. It does this by comparing the files and folders in the original folder to each user-specific folder. If it finds any missing in the user-specific folder, it copies them from the original folder.

The trick is that the comparison is on existence only: the actual contents of the files are not examined. This allows SSMS to install (and repair) from the original Templates, but also lets the use modify their local copies without SSMS overwriting them with original files at the next program run.

So, if you edit a Template, your changes will remain forever. But, if you delete a Template, you'll find it's re-appeared at the next program run. (If you delete both the original and local Templates - which you should not do - SSMS won't be able to perform this copy, because it won't be able to find the file.)

Conclusion

Using SSMS Templates is less error prone than typing code by hand, faster than using the GUI, and makes it possible to add documentation while it's still fresh in your mind. The only thing better would be if you could create your own Templates. That will be the subject of a future post.