SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Using Templates

Templates are one of those really handy tools that most DBAs I know of have either never heard of or just don’t use much. I have to admit I fall into the second category myself. I will break out templates occasionally when I’m working with something I don’t do much. Cursors for example (please keep the yelling and screaming to a minimum).

To open the template pane either go to VIEW-Template Explorer or hit Ctrl-Alt-T.


The template pane has a list of the “templates” that come with SQL Server and any templates that you create for yourself.


Using a Template:

Let’s say that you want to create a partitioned table and haven’t done one in awhile. First go to the partition function option in the template pane and click the +.


Next we are going to select “Create Partitioned Table”. There are three ways we can do this. We can right click and select “Open”, double click on it, or drag it into a query window. Either way we get this.


Now we can fill in the template parameters. The template parameters are in the format of . Again there are 3 ways (that I know of) to fill them in. Probably the hardest, fill them in by hand. The next two are about the same. Select Query->”Specify Values for Template Parameters …”, or select Cntrl-Shift-M.


Note that each template parameter has a name, a data type, and a default value. In this case I’m going to leave schema_name and input_parameter_type as the defaults and fill in some simple values for the others.


Hit OK and the values will be filled into the template parameters.


Now in this case the partition schema needed a little work and I changed it from.

 -- Create partition scheme
	PARTITION fn_PartitionTest TO ([PRIMARY], TransactionHistoryGroup, WorkOrderGroup)


 -- Create partition scheme

Because I don’t have those filegroups in my AdventureWorks database. If this were for real I would probably also need to change the default for the Partition Function as well. But once those minor changes are made I end up with a partitioned table called dbo.tbl_PartitionTest.

Now let’s say I want to make a few minor changes. In my case this particular template specifies the AdventureWorks database and I want that as another parameter. Not to mention the fact that mine is AdventureWorks2008, so I want a different default.

First I go back to the template exporer and right click on the template. I then select “Edit”.


Next I replace

USE AdventureWorks


USE <database_name, sysname, AdventureWorks2008>

Note in the template parameter the first part is the parameter name, the next is the datatype and last is the default value.

And lastly I save by clicking Cntrl-S or File-Save.

You can of course use this exact same method for creating brand new templates by right clicking on a template folder and selecting New-Template, name it, then edit it.


My name is Kenneth Fisher and I am Senior DBA for a large (multi-national) insurance company. I have been working with databases for over 20 years starting with Clarion and Foxpro. I’ve been working with SQL Server for 12 years but have only really started “studying” the subject for the last 3. I don’t have any real "specialities" but I enjoy trouble shooting and teaching. Thus far I’ve earned by MCITP Database Administrator 2008, MCTS Database Administrator 2005, and MCTS Database Developer 2008. I’m currently studying for my MCITP Database Developer 2008 and should start in on the 2012 exams next year. My blog is at www.sqlstudies.com.


Leave a comment on the original post [sqlstudies.com, opens in a new window]

Loading comments...