Template Explorer Gold Mine in SQL Server Management Studio

Sometimes as a newbie to SQL Server using SQL Server Management Studio (SSMS) you don’t know about the little gold mines that are within the product that help you be a better DBA or Developer. Template Explorer is one of those. To get to it go to VIEW from the taskbar then Template Explorer. Alternatively, you can reach it with keyboard shortcut Control+Alt+T.

When you click on it, another side window will appear showing you a list of all templates available within Management Studio.

A basic example would be creating a new stored procedure, this template provides an easy starting point. Microsoft has even gone so far as to incorporate parameters to make it easy for you to add values. You can use the Replace Template Parameters dialog box to insert values into the script. Get to it by clicking Ctrl+Shift+M.

-- ================================================

-- Template generated from Template Explorer using:

-- Create Procedure (New Menu).SQL

--

-- Use the Specify Values for Template Parameters

-- command (Ctrl-Shift-M) to fill in the parameter

-- values below.

--

-- This block of comments will not be included in

-- the definition of the procedure.

-- ================================================

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

-- =============================================

-- Author:           <Author,,Name>

-- Create date: <Create Date,,>

-- Description:      <Description,,>

-- =============================================

CREATE PROCEDURE <Procedure_Name, sysname, ProcedureName>

       -- Add the parameters for the stored procedure here



       <@Param1, sysname, @p1> <Datatype_For_Param1, , int> = <Default_Value_For_Param1, , 0>,

       <@Param2, sysname, @p2> <Datatype_For_Param2, , int> = <Default_Value_For_Param2, , 0>

AS

BEGIN

       -- SET NOCOUNT ON added to prevent extra result sets from

       -- interfering with SELECT statements.

       SET NOCOUNT ON;



    -- Insert statements for procedure here

       SELECT <@Param1, sysname, @p1>, <@Param2, sysname, @p2>

END

GO

Although many of us know how to do create a stored procedure, how many of you know the syntax for creating a partition function, building your own statistics or even creating a full text catalog?

There are so many templates available not only to those new to SQL Server but to those of us that have been around for a while. Many of us take these for granted and many still don’t even know they exist. Take a look for yourself. It seems like the list seems to grow all the time.

On top of that, SSMS also has templates for Analysis Services cubes. Just click on the cube icon in the Template Browser to get to this list. Writing MDX is not my favorite thing to do, so I always turn to a template when starting something new.

As a bonus, you can make your own templates. Right click under a folder in the template browser and choose NEW. Next name the new template then right click and choose EDIT. This will open a query window you can use to create your template.  It’s that easy.

Note: All templates can be found on your local SSMS install at C:\Users\[yourusername]\AppData\Roaming\Microsoft\SQL Server Management Studio\14.0\Templates\Sql . If you are unable to find it enable hidden folders.