SQL SERVER – Template Browser – A Very Important and Useful Feature of SSMS

Let me start today’s blog post with a direction question. How many of you have ever used Template Browser? Template Browser is a very important and useful feature of SQL Server Management Studio (SSMS). Every time when I am talking about SQL Server there is always someone comes up with the question, why there is no step by step procedure included in SSMS for features. Honestly every time I get this question, the question I ask back is How many of you have ever used Template Browser?

I think the answer to this question is most of the time either no or we have not heard of the feature. One of the people asked me back – have you ever written about it on your blog? I have not yet written about it. Basically there is nothing much to write about it. It is pretty straight forward feature, like any other feature and it is indeed difficult to elaborate. However, I will try to give a quick introduction to this feature.

Templates are like a quick cheat sheet or quick reference. Templates are available to create objects like databases, tables, views, indexes, stored procedures, triggers, statistics, and functions. Templates are also available for Analysis Services as well. The template scripts contain parameters to help you customize the code. You can Replace Template Parameters dialog box to insert values into the script. Additionally users can create new custom templates as well with folder structure.

To open a template from Template Explorer Go to View menu >> Template Explorer or type CTRL+ALT+T.

You will find a list of categories click on any category and expand the folder structure.

For our sample example let us expand Index Folder. In this folder you will notice the various T-SQL Scripts. These scripts can be opened by double click or can be dragged to editor area and modified as needed.

Sample template is now available in the query editor area with all the necessary parameter place folder. You can replace the same parameter by typing either CTRL+SHIFT+M or by going to Query Menu >> Specify Values for Template Parameters.

In this screen it will show  Specify Values for Template Parameters dialog box, accept the value or replace it with a new value. This will now get your script ready to go. Check it one more time and change the script to fit your requirement.

I personally use template explorer for two things. First one is obviously for templates but the hidden one and an important one is for learning new features and T-SQL commands. There is so much to learn and so little time.

Reference: Pinal Dave (http://blog.sqlauthority.com)

14 thoughts on “SQL SERVER – Template Browser – A Very Important and Useful Feature of SSMS

  1. Hi Pinal,

    Thanks for this post.
    I regularly use this feature.In your post, you haven’t specified how to add new template and how to modify existed one.

    Right Click on SQL Server Templates folder –>New–>
    There we can create new folder or Template.

    if we want to use the template middle of our query window, we can drag and drop in the template in to our query window.

    Example: I ll save the syntax those are, i wont use regularly,When i need those syntax middle of my query, i just open template explorer and drag and drop into middle of my query window.


  2. Pingback: SQL SERVER – Remove Cached Login from SSMS Connect Dialog – SQL in Sixty Seconds #049 | SQL Server Journey with SQL Authority

  3. Pingback: SQL SERVER – Weekly Series – Memory Lane – #035 | Journey to SQL Authority with Pinal Dave

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