Intemperate Templates

Even though I spend most of my time these days working in the SSIS and SSAS designers in Visual Studio [1] I still visit SQL Server Management Studio (SSMS) quite a bit. I was doing some work in SSMS this morning and was having trouble remembering the syntax for what I needed to do. Instead of looking things up in Books Online like I usually would, I went to the Template Explorer window, thinking to save myself a little typing.

Template Explorer is not visible by default (at least I don't think it is - I don't have a default installation of SSMS kicking around today) but you can hide it or show it by using the SSMS View menu. As you can see below, it displays a tree view of server and database objects, and within each folder in the tree is a list of templates.



When you double-click on a template (for this example I chose CREATE TABLE) you will get a new query window in SSMS that contains an example of the selected operation with for parameters that you can customize, as shown below.



At this point you can simply edit the text of the template in the query window, but you can also use a poorly-documented[2] feature of SSMS to automate the edits. As shown below, if you click on the SSMS Query menu, there will be a "Specify Values for Template Parameters..." menu option displayed.



When you select this menu option, you'll be presented with a dialog box that looks like this:



It's relatively simple to edit the template parameters, to end up with something that looks a little like this:



When you click OK, the text in the query window will be updated with the values you supplied, looking more like this:




The nice thing about this[3] is that if a given parameter (in this example, think of the table name and the PK field name) appears multiple times in the template, you need to only enter its value once in the dialog box for it to be replaced consistently in all places within the template, which is pretty darned handy.

Of course, there is still room for improvement. For example:

1. The template parameter dialog box is modal, so if you forget something (like the name of the database[4]) you cannot navigate around SSMS to find it out.
2. There are not nearly as many templates as you'd like to see, although to be completely fair you can also create your own custom templates.
3. The templates that do exist are inconsistent in their design and quality. For example, if you use the CREATE TABLE template you get an "IF EXISTS" block so you can run the script as many times as you want. But if you use the CREATE SCHEMA template you don't get anything like this - you can either remember to only run the script once or else you need to manually add the logic you need.

Still, despite their flaws, SSMS templates are a good tool if you have occasional syntax failures like mine this morning. Enjoy!

[1] Or else in Word, Outlook, PowerPoint and Visio...

[2] In any event, I've always stumbled across it and not seen it documented anywhere, which is why when I remembered it this morning ("hey, isn't there a menu option somewhere?") I decided to blog about it.

[3] I point this out because nine times out of ten I'd rather edit code in a text editor and not in a dialog box, so there needs to be some real value here or else you're just adding work, not lessening it.

[4] Not that I would ever do this, of course...