Building and customizing SQL Server script templates

By: Tim Ford | Read Comments (2) | Print Tim is a Senior Database Administrator and SQL Server MVP. He has been working with SQL Server since 1999. Related Tips: More

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.

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.

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.

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:

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

Related Tips: More | Become a paid author

Last Update: 3/28/2008

Share:

Share

Tweet

Connect with MSSQLTips.com Follow us on twitter... Let's connect on facebook! Join us on Google+

Comments and Feedback:

Tuesday, April 22, 2008 - 9:45:58 AM - KG_DBA	Read The Tip
How do you change the location of the templates to a shared location so that you can share templates with a team?

Friday, May 09, 2008 - 5:10:08 PM - timmer26	Read The Tip
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.

Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name

*Email

Notify for updates

Comments *** NOTE *** - If you want to include code from SQL Server Management Studio (SSMS) in your post, please copy the code from SSMS and paste the code into a text editor like NotePad before copying the code below to remove the SSMS formatting.
*Enter Code

 

Sponsor Information

"Amazing, Amazing, Amazing! SQL doctor is truly one of the most powerful tools I have seen." Time-strapped database professionals use SQL Monitor to look after their servers. Try it online. Need SQL Server help and not sure where to turn? Reach out to the Edgewood experts for a Health Check starting at $995. Get SQL Server Tips Straight from Kevin Kline. Solving SQL Server problems for millions of DBAs and Devs since 2006. Join now. Are you waiting on SQL Server? Learn about these DMV's.