Peronal Template in SQL Server Management Studio

It has been a while since my last post. Well, there are so many jobs need to be done during this busy season. When we are busy, we need to find a way to improve our productivity, or simply free us from repeated jobs. Today let’s see a very important function in SSMS Template.

When we have SSMS installed, by default there will be standard system templates available. You can open the Template Browser by using the hot key “Ctrl + Alt + T”, or by using the command item from the menu View -> Template Explorer.



Applying the template is quite simple, simply find the template you want to use, double click it, and then replace the variables in the template. Let’s see an example:

Assume I want to create a new function by applying the function template. To do so, I double click the “Create Inline Function (new menu)” template under the Function category.



At this stage a new query window with default pattern should appear.



As you can see the template pattern contains comments section, script section, and lots of “<>”. These are the parameters can be quickly replaced. As specified by the instruction comments, we can use hot key “Ctrl + Shift + M” to replace the parameters, as shown below.



After we input the values, click OK button and we will have the function populated within the query window.



This is a standard function provided in SSMS. Obviously it can save us lots of time. But we can make it even better, i.e. we can create our own parameterised template!

Firstly, we need to have a blank folder. In below screenshots, I created a personal template folder by right clicking top-most node “SQL Server Templates” and selecting New -> Folder, and then named it “Personal Template”.



Right click the newly created folder and this time, I selected New -> Template. Now we will have an empty template available in the folder.

To fill in our template, we need to right click the template, and then select Edit, as shown below.



Now the template should be opened in SSMS. Just like example shown below, to indicate the parameter, we need to use the format “<name="" type="" example="">”. To make it simple, in this example I just created a "If exists, select" pattern.

 USE <database_name, sysname, master>  
 GO  
 IF EXISTS (SELECT 1 FROM sys.tables where name = '<table_name, sysname, dummy_table>')  
 BEGIN  
      SELECT * FROM <table_name, sysname, dummy_table>  
 END  

After we done everything, we can save it by using “Ctrl + S” or using the menu bar Save command. Close the template query window, now double click our personal template and press “Ctrl + Shift + M”, you should see parameter window appears.



Fill in values for the parameters, close the popup window by pressing OK button, you should see the result as expected. Handy function, isn’t it?