Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Building and customizing SQL Server script templates


By:   |   Read Comments (5)   |   Related Tips: More > SQL Server Management Studio

Attend our free MSSQLTips Webcast - How to Simplify Routine SQL Server Administration Tasks


Problem

All that SQL Server POWER!  All that SQL Server FUNCTIONALITY!  There is not enough time in the day to keep it all straight!  There is no shame in making use of any short cuts or cheats that allow you to be a more productive and well-versed SQL Professional in order to cope with the sheer volume of expectations you are required to meet on a daily basis.  One powerful, but often-overlooked tool at your disposal is the Template Explorer that is a core component of SQL Server Management Studio.  In this tip we expose the Template Explorer; making use of the "canned" templates that ship with SSMS, and altering those templates to conform to all those quirks that make our environments unique.  I will also show you how to create your own templates from existing sql scripts.

Solution

The Template Explorer in SQL Server Management Studio exposes a repository of templates for use in query construction.  These templates provide the framework of queries used to perform a myriad of routine (and not so routine) processes in Microsoft SQL Server:  from database backup and restores to creating Service Broker queries and partitioning schemes.  There are templates not only for SQL 2005, but previous versions of MSSQL all the way back to 7.0.

The Template Explorer is accessible by one of two actions:

  • Select View and then Template Explorer from the SSMS menu bar
  • Use the shortcut key combination of Ctrl+Alt+T

Either of these two actions will display the Template Explorer in a dockable window within SQL Server Management Studio.

ssms template explorer

Under a default installation, the templates will be located under C:\Program Files\Microsoft SQL Server\90\Tools\Binn\VSShell\Common7\IDE\sqlworkbenchprojectitems\Sql.  They will always be preserved in the RTM state within this folder.  Edits to the these templates, as well as templates you create will be saved under C:\Users\username\AppData\Roaming\Microsoft\Microsoft SQL Server\90\Tools\Shell\Templates\Sql in Windows Vista or in C:\Documents and Settings\username\Application Data\Microsoft\Microsoft SQL Server\90\Tools\Shell\Templates\Sql in Windows XP (where username is the context of the current user logged into the workstation) in order to support multiple users accessing the same installation of Management Studio.  

Take note that SQL Server Management Studio includes templates for Database Services, Analysis Services, and SQL Server Compact Edition.  You can switch between the available templates for each of these products by selecting the appropriate icon at the top of the Template Explorer window.  For the purposes of this discussion we're going to focus exclusively on Database Services templates, with the understanding that the behavior of all templates are consistent with the remainder of this discussion. 

A SQL template appears identical to a normal SQL query script with only a single noticeable exception:  the parameter block.  A template is no more than a saved SQL script with parameter blocks that will be swapped out prior to execution.  Parameter blocks are in the format of <parameter_name, data_type, default_value>.  Let's take a look at the content of the create database template located under the Database folder in Template Explorer

-- ============================================= 
-- Create database template 
-- ============================================= 
USE master 
GO 

-- Drop the database if it already exists 
IF  EXISTS ( 
   SELECT name  
       FROM sys.databases  
       WHERE name = N' <Database_Name, sysname, Database_Name>' 
) 
DROP DATABASE  <Database_Name, sysname, Database_Name> 
GO 

CREATE DATABASE  <Database_Name, sysname, Database_Name> 
GO   

You'll note that there is only a single parameter required in this template: Database_Name.  The parameter will expect a sysname data type and if not supplied, the value will default to 'Database_Name'.  Microsoft provides you with two different methods of entering parameter values:

  • Select Query, then Specify Values for Template Parameters from the SSMS menu bar
  • The Ctrl+Shift+M shortcut key combination

Using either method will display the Specify Values for Template Parameters dialog as displayed below.

template parameters

I've entered Foo as the value for the Database_Name parameter.  Once done, click OK.  You'll notice that all the parameter blocks referencing the Database_Name parameter have been replaced by the value of 'Foo' that I supplied in the parameter dialog. 

-- ============================================= 
-- Create database template 
-- ============================================= 
USE master 
GO 

-- Drop the database if it already exists 
IF  EXISTS ( 
   SELECT name  
       FROM sys.databases  
       WHERE name = N'Foo' 
) 
DROP DATABASE Foo 
GO 

CREATE DATABASE Foo 
GO   

Now it is only the matter of executing the query and my Foo database will be created.  In the case of this template it will create the database using all the default setting for database creation via the model database.  The data and log files will be created in the default data and log file locations.  Suppose, however, that you want to be able to specify where the database files are created.  You can simply create a new template, add template code or code from a frequently-used script, modify and parameterize the code, save it, and run it as needed.  Let's take a look at that process in detail.

Step One:  Create a new template

In order to create a new template, right click on an existing template in the Template Explorer and select New, then Template from the pop-up menus.  Name the template.  In the example below I named it create database - specify files.

ssms template explorer

Step Two: Add, alter, and parameterize the template code

For this example I am going to be building off of the existing create database template we reviewed earlier.  I will be adding code for creating the data and log file locations in the Windows file system if they do not already exist.  I've also modified the template code I pasted from the create database template to include parameters for logical and physical file information for the database.  The code to create the Windows directories was supplied in a previous tip. 

-- ============================================= 
-- Create database template:  Modified to Include Create Directory 
-- ============================================= 
USE master 
GO 

DECLARE @DirTree TABLE (subdirectory NVARCHAR (255), depth INT)   --Temp table for directories 

-- ============================================= 
--Create the data directory if it does not already exist 
-- ============================================= 
INSERT @DirTree (subdirectory, depth) 
EXEC master.sys.xp_dirtree N' <Data_Path, nvarchar(500), Data_Path>' 

IF NOT EXISTS (SELECT 1 FROM @DirTree WHERE subdirectory = N' <Database_Name, sysname, Database_Name>') 
   EXEC master.dbo.xp_create_subdir N' <Data_Path, nvarchar(500), Data_Path>' 

-- ============================================= 
--Create the log directory if it does not already exist 
-- ============================================= 
INSERT @DirTree (subdirectory, depth) 
EXEC master.sys.xp_dirtree N' <Log_Path, nvarchar(500), Log_Path>' 

IF NOT EXISTS (SELECT 1 FROM @DirTree WHERE subdirectory = N' <Database_Name, sysname, Database_Name>') 
   EXEC master.dbo.xp_create_subdir N' <Log_Path, nvarchar(500), Log_Path>' 

-- ============================================= 
-- Drop the database if it already exists 
--This code did not change from the original template 
-- ============================================= 
IF  EXISTS ( 
   SELECT name  
       FROM sys.databases  
       WHERE name = N' <Database_Name, sysname, Database_Name>' 
) 
DROP DATABASE  <Database_Name, sysname, Database_Name> 
GO 

-- ============================================= 
CREATE DATABASE  <Database_Name, sysname, Database_Name> ON PRIMARY 
--This code changed from the original template.   
--Parameters for logical and physical file information was added 
-- ============================================= 
   ( 
   NAME = N' <Data_File_Name, sysname, Data_File_Name>',  
   FILENAME = N' <Data_MDF_Path, nvarchar(600), Data_MDF_Path>'  
   ) 
 LOG ON  
   ( 
   NAME = N' <Log_File_Name, sysname, Log_File_Name>',  
   FILENAME = N' <Log_LDF_Path, nvarchar(600), Log_LDF_Path>'  
   ) 
GO 

Step 3:  Save the template code

It is crucial that you save the template prior to supplying parameter values.  Otherwise you're left with a SQL script, not a template.

Step 4:  Supply parameter values and run the script

Entering values into the Specify Values for Template Parameters dialog is slightly more involved with this template:

template parameters

Once the parameter values have been supplied, you're left with an executable script that will create the Foo database in the location specified.

-- ============================================= 
-- Create database template:  Modified to Include Create Directory 
-- ============================================= 
USE master 
GO 

DECLARE @DirTree TABLE (subdirectory NVARCHAR (255), depth INT)   --Temp table for directories 

-- ============================================= 
--Create the data directory if it does not already exist 
-- ============================================= 
INSERT @DirTree (subdirectory, depth) 
EXEC master.sys.xp_dirtree N'c:\temp\Data\Foo' 

IF NOT EXISTS (SELECT 1 FROM @DirTree WHERE subdirectory = N'Foo') 
   EXEC master.dbo.xp_create_subdir N'c:\temp\Data\Foo' 

-- ============================================= 
--Create the log directory if it does not already exist 
-- ============================================= 
INSERT @DirTree (subdirectory, depth) 
EXEC master.sys.xp_dirtree N'c:\temp\Logs\Foo' 

IF NOT EXISTS (SELECT 1 FROM @DirTree WHERE subdirectory = N'Foo') 
   EXEC master.dbo.xp_create_subdir N'c:\temp\Logs\Foo' 

-- ============================================= 
-- Drop the database if it already exists 
--This code did not change from the original template 
-- ============================================= 
IF  EXISTS ( 
   SELECT name  
       FROM sys.databases  
       WHERE name = N'Foo' 
) 
DROP DATABASE Foo 
GO 

-- ============================================= 
CREATE DATABASE Foo ON PRIMARY 
--This code changed from the original template.   
--Parameters for logical and physical file information was added 
-- ============================================= 
   ( 
   NAME = N'Foo_Data',  
   FILENAME = N'c:\temp\Data\Foo\Foo_data.mdf'  
   ) 
 LOG ON  
   ( 
   NAME = N'Foo_Log',  
   FILENAME = N'c:\temp\Data\Foo\Foo_log.ldf'  
   ) 
GO 

Templates are a very powerful, yet under-used tool available to any SQL Professional using Microsoft SQL Server Management Studio.  The Template Explorer provides a simple, centralized repository embedded in SSMS that if used consistently can virtually eliminate the need to maintain a script repository elsewhere in your environment.  The templates are easy to utilize and just as easy to create.  They provide the ability to "operationalize" many of your repetitive functions, freeing you up to address all those other high priority issues that we can never seem to get away from.

Next Steps
  • Make use of the RTM templates available in SQL Server Management Studio.
  • Create your own templates to operationalize your repetitive tasks.
  • Review this tip on creating Windows directories within T-SQL. 
  • Stay tuned for a future template tips at MSSQLTips.com


Last Update:


signup button

next tip button



About the author
MSSQLTips author Tim Ford Tim Ford is a Senior Database Administrator and SQL Server MVP. He has been working with SQL Server since 1999.

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Thursday, April 17, 2014 - 12:22:08 AM - Newbie Back To Top

I am trying to capture the parameter values  from the template and store in a separate table. Is there any way I can achieve this?  I have this immediate requirement . Thanks in advance .


Monday, January 06, 2014 - 8:05:21 AM - Scott Gall Back To Top

My apologies that this is such an old post, only hoping it's still monitored as the info is still relevant in SSMS 2012...

I was wondering if it is possible to add variables which automatically populate in the Value Column of ?

 

for example (note this does not evaluate on the OK of the dialog merely an example of how I think it could work):

"<TodaysDate, datetime, GETDATE()>"

 

Thanks in advance.

 


Wednesday, March 27, 2013 - 1:36:47 PM - Kevin Back To Top

I'm only five years late to join the conversation!  Maybe things have changed, but here is how you can share templates now:

Create a new SQL script, following the parameter convention of <parameter, datatype, defaultvalue>.  You can save this file to a shared drive, shared solution, email it out, whatever you want, it's just a .sql file.

If you are going to keep it on a shared drive, you may want to add a comment of some sort so people don't accidentally write over it.

For example, you could copy this at the very top of the script:

/*Copy this script into a new query window and then press Ctrl+Shift+M*/

 


Friday, May 09, 2008 - 5:10:08 PM - timmer26 Back To Top

That is not a configurable option specifically to prevent against users altering other users' templates.  I suppose you may be able to hack your registry in order to change this to a UNC or mapped drive but I would not advocate doing so. 


Tuesday, April 22, 2008 - 9:45:58 AM - KG_DBA Back To Top

How do you change the location of the templates to a shared location so that you can share templates with a team?


Learn more about SQL Server tools