SSMS Day 6: Leveraging Templates

31 Days of SSMS: Table of Contents

Today is Day 6 in the series. Day 2 of leveraging generated code. So let’s get to it.

Open up SSMS and hit Ctrl+Alt+T. What just popped up is the Template Browser. Stored within SSMS are dozens of common SQL statements all stubbed out and ready to use. Just a couple keystrokes for example and you are 80% of the way to a full database backup statement. Double click any of them to open in the query window.

As I work around both DBAs and database developers I’m always surprised I don’t see this getting used more. It’s a really nice little feature. One, it’s a great way to save keystrokes and two, it’s a fun place to go look around and brush up on statements you don’t use very often.

You can access Templates with the hotkeys given or it’s also under the View menu.

For as many people who don’t regularly use the Template Browser, there are probably far less who are aware that SSMS also provides a very easy way to complete the statement by filling in each of the template parameters via a simple dialog window.

Filling in Parameters

With a template open in your browser hit Ctrl+Shift+M. This will bring up a dialog that let’s you specify values for each of the template parameters. You can also open from the menu under Query->Specify Values for Template Parameters or click the little A->B icon on the toolbar.

I’ll admit templates take some of the intimacy out of writing your own statements from scratch, but you can still add your own personal touch. You can edit existing templates or simply create your own. In fact, setting up a folder node under the Template Browser filled with your own regularly used queries is a great way to keep a simple code library handy. Learned that one from @BrentO.

Making Your Own Templates

Adding or editing templates is as easy as right clicking. There aren’t a lot of options – add, edit, open, and new folder. In fact a common complaint with templates is that you can’t control much else. Where they are stored, for example. (psst, C:\Users\ <UserName> AppData\Roaming\Microsoft\Microsoft SQL Server\ <version> \Tools\Shell\Templates\Sql\ )

The good news however is that you CAN customize your own parameter placeholders when creating your own so that they work just like the ones already in SSMS. Just enclose parameters in angle brackets with the following format: <parameter_name, type, default_value>.

To see it in action paste the following into your query window and then hit Ctrl+Shift+M and give it a try.

SELECT * FROM dbo.kidToys 
WHERE Toy = N'<Pokemon_Character, sysname, Charizard>'

If you were paying attention you just figured out that the source of a script doesn’t necessarily have to from the Template Browser to take advantage of the Specify Values dialog. Knowing this might come in handy for you architects out there who have to stub out code for others.

Templates are very useful. But they do have some limitations – for example you can’t control where they are stored (though you can use GIT to create a repository).

Tomorrow we’ll explore something similar but with a few different options. Code Snippets.