By: Tim Ford | Comments (5) | Related: > SQL Server Management Studio
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
About the author
Tim Ford is a Senior Database Administrator with MindBody.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
