Templates in SQL Server Management Studio

SQL Server

One feature of SQL Server Management Studio (SSMS) that use isn’t used very much is the template feature. The Template Explorer in SSMS exposes a repository of pre-created templates for use in your query construction or for general knowledge.  These free templates provide the framework of queries used to perform a variety of routine processes in SQL Server, including   database backup, database restores,  inserting data, etc.

A SQL template appears identical to a normal SQL query script with a single exception:  the parameter block.  A template just a saved SQL script with parameter blocks that will be swapped out prior to execution.

In your saved script, just use the this simple format to denote the parameters for replacement. The format is simple:

 <parameter_name, data_type, value>

When you then open the template, you use CTRL + Shift + M, and you will get a simple pop-up asking you to enter your replacement values. How do you add your own template:

  1. On the View menu, click Template Explorer.
  2. In Template Explorer, navigate to the node where you would like to store the new template.
  3. Right-click the node, point to New, and then click Template.
  4. Type the name for your new template and then press ENTER.
  5. Right-click the new template, and then click Edit. In the Connect to Database Engine dialog box, click Connect to open the new template in Query Editor.
  6. Create a script in Query Editor. Insert parameters in your script in the format <parameter_name, data_type, value>. The data type and value areas must be present, but can be blank.
  7. On the toolbar, click Save to save your new template.


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