SSMS templates in SQL Server 2012

There is nothing new about using templates in SSMS. Each time you create let’s say a new stored procedure you use a template like this one

image

They have been around since SQL Server 2000.

Where is my template parameters pop-up dialog ?

In previous versions of SQL Server you wanted to use the key combination Ctrl-Shift+M to pop-up the parameter dialog window and write the parameters and other stuff specified by the template. Like that :

image

Well the bad news is that the combination Ctrl-Shift+M doesn’t work in SSMS 2012. I guess they forgot to change the templates header when the new SQL Server version was released. The good news is that the templates still work. The key for the Template Parameters window is now Alt+Q+S.

The Template Explorer

I don’t know if you never used it but templates are a powerful time saver for writing common T-SQL expressions. For that purpose there is a template explorer hidden by default but accessible from the View menu or by using the shortcut Ctrl-Alt+T.

image

Even it is called the Template Explorer, the windows that shows is called The Template Browser…go figure…Anyway this explorer exposes 2 categories of templates : SQL Server templates and Analysis Services templates.

image

With more 40 template folders from Aggregate to XML Schema collections you will surely find one that suits you.

Creating you own templates

Of course this is the right place to create your own templates. Let’s say that you want to create a full Service Broker setup with Service, Queue, Contract, Message Types and Routes that should be distributable and customizable so the setup could be rolled on different instances or databases, accommodating changes in the Service Name or the Route’s address.

Let’s start by creating your own folder to organize those templates

For doing that right-click on SQL Server Templates –> New –> Folder and give it a name.

Tip : I usually prefix the folder name with a comma so I’m sure that it will always show on top of the list.

Once the folder is created you can right-click on the folder –> New –> Template. and you’re ready to write your T-SQL (or DMX, MDX,XMLA if you chose to create your folder in the Analysis Services  category).

the only thing you need now – except from writing an useful template – is to make good use of parameters in your template. The syntax is the following :

<Parameter name , data type , default value > you can omit every single one as long as you remember the commas so it would resemble something like that <,,> . I would though recommend that you at least give it a name !

image

Once you have written your template and saved it if you double-click on it it will open a new query window with a copy of the template ready to use.

When pressing Alt+Q+S something similar to this window should show up

image

letting you enter the values you want.

Editing the templates

Keep in mind that double-clicking on the template will open a copy of it. If you want to edit a template you’ll have to right-click it and choose “Edit”.

Where are my templates?

In case you are wondering where all the templates are…

C:Users<UserName>AppDataRoamingMicrosoftSQL Server Management Studio11.0TemplatesSql

Happy customizing !!

Régis

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: