SQL Server Management Studio has a not well known but very handy feature for using and creating template scripts. This article will provide an overview of the Template Explorer in SQL server Management Studio and dive into examples of using existing templates as well as creating your own.
Templates and the Template ExplorerSQL Server comes standard with several templates of various shapes and sizes. To view the templates in SQL Server Management Studio, navigate to the View menu and then down to Template Explorer or press Ctrl + Alt + T.
Figure 1 - SSMS View Menu
The Template Explorer opens inside SQL Server Management Studio as a pane. In the picture below you can see that it has opened on the right hand side. Observe the vast array of template categories provided out-of-the –box. (This image is from SQL Server 2008 R2.)
Figure 2 - SSMS with Template Explorer open
Need to configure Database Mail, for example, and don't know where to start? Simply open the Database Mail folder in Template Explorer and double-click the Simple Database Mail Configuration template to get you started in the right direction. When the template opens, you see before you the SQL script to accomplish the task. All you need to do is fill in the blanks, so to speak. In this case, the parts that require customization are contained within less than and greater than sign such as in the example below.
You don't need to change these parameter placeholders out by hand, however. Under the Query menu is an item titled Specify Values for Template Parameters.
Figure 3 - Specify Template Parameters on Query Menu
Clicking on this pops up the dialog box shown below.
Figure 4 - The Specify Template Parameters Dialog Box
Substitute in your values into this dialog. Click the OK button and the placeholders are all replaced with your parameter values. Your template is now customized and ready to run.
Create Your Own Template
What's even better than a bunch of canned SQL Server scripts? A place to store your own canned scripts specific to your databases and your repetitive tasks. Within Template Explorer, you can add and organize your own templates. We'll add one here to demonstrate.
First we'll create a folder in Template Explorer to hold our new template so we can easily find it later. In Template Explore, right-click the top-level folder called SQL Server Templates. On the drop down menu choose New and then Folder.
Figure 5 - New Template Folder
This action places a folder at the bottom of the Template Explorer titled New Folder and ready to be renamed. Give it a name of your choosing.
Now right-click on your new folder and choose New and then template. This creates a new sql script within the folder. By default, the new SQL script is called New SQL Server Template. Right-click the SQL Script entry and click Rename from the resulting menu to give the script a proper name of your choosing.
Figure 6 - New Template
Figure 7 - New Template Script Created
Double-clicking the script opens the template in a new SQL query window. (You could also right-click the template and choose Edit.) You are presented with a blank SQL script.
Simply write your SQL statements as you would to complete the task at hand only remembering to break out all of the configuration — anything that could change from one execution to the next. These will become your parameters.
Below I will demonstrate creating a script with the parameters broken out so the script is template ready to have its parameters filled in and the script executed. For the purposes of demonstration, assume I've been called on to manage the creation of our national customers in a centralized database to then be replicated down to our individual stores.
I create my template in Template Explore and bring it up. I type in my script to add the national customer, as below. (Keep in mind this script is shortened and for demonstration purposes only.)
Notice that the SET statements use the parameter placeholder syntax (parameter_name, data_type, value).
Parameter Name is required. Data Type and Value can be blank if you choose, but you still have to include the commas and the less than and greater than signs.
Specifying this in place of a value for the SET statement tells SQL Server how to dynamically fill in the Specify Template Parameters dialog box we used earlier on the out-of-the-box templates. Here is that dialog for my new template.
Cancel out of the dialog and save your template. It is now ready for use.
The Template Explorer inside SQL Server Management Studio not only provides a host of pre-written, highly useful queries to jumpstart some of the not-so-familiar tasks, but also a place to store and organize your own parameterized queries.