Quick Tip 005 – SSMS Using Templates

I often find that not many people truly harness the power of SQL Server Management Studio (SSMS).  In the next few quick tips, I’m going to highlight some nice functionality that can help in your everyday use of SSMS.

In this tip, I’m going to talk about Templates, how to use them, how to create your own and where on your system they are being stored.

Accessing Templates

You can access templates in SQL Server Management Studio from the following menu:

View | Template Explorer, alternatively you can press CTRL+ALT+T

By default SSMS comes with a whole host of pre-installed templates.  That make life a lot easier when trying to create database objects.

Navigate to the Database folder and open the Create Database template file.

 

You’ll notice that the template includes place holders e.g. <Database_Name, sysname, Database_Name>

The place holders actually have a very specific format that is <Parameter, Type, Value>

• Parameter: The name of the parameter
• Type: The data type of the parameter
• Value: A default value to be used

Now the upside of using this format is that these place holders are recognised by SSMS, which then allows you to use another feature; Specify Values for Template Parameter.

Specify Values for Template Parameter

This option is available under Query | Specify Values for Template Parameter, alternatively you can press CTRL+SHIFT+M. 

This brings up a window that then allows you to replace the content of the place holders with actual values.  SSMS iterates through all the place holders in the script and comes up with a unique list of placeholders which are displayed to the user, in this example the screen looks like this:

 

Change the name in the Value column to “Sample” and click OK

Your screen should now look like this.  Notice that all three instances of the placeholder where changed to the same value.

Creating your own templates

Templates are a great way to create standardisation in your code, however for this functionality to be useful you want to be able to create your own templates, and you can.  To illustrate this we are going to create a template to put a database into a read only state.

Right click on the Database folder in the Templates Explorer window, and Select New | Template

Call this template “Read Only Database”

In the Query Editor window past the following code:

-- =============================================
-- Read Only Database
-- =============================================

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>'
)

ALTER DATABASE <Database_Name, sysname, Database_Name> SET READ_ONLY
GO

Save and close the file.  Double click on the template that you just created, press CTRL+SHIFT+M to bring up the Specify Values for Template Parameter window and replace the Database_Name Value with Sample.

Again you’ll notice that both instances of the place holders were replaces with the word Sample.  Congratulations you’ve successfully created your first template.

Template Storage

Now that you’ve created your template, you might be interested to find out where its actually being saved.  Before we do this go an delete all the other files the database folder.  The folder structure should now look something like this:

Close down SSMS, and re-open it.  If the Template Explorer is not visible press CTRL+ALT+T.  Now navigate back to the Database folder.  You’ll notice that all the files that you just deleted have remarkably reappeared.  Why is this?  The answer is in how the templates are stored.

The templates are actually stored in two locations, there is a set of the original that are stored when you install sql server, these are stored here:

C:\Program Files\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\SqlWorkbenchProjectItems

Whenever you open SSMS the files are copied from this location to a working copy stored here:

C:\Users\<username>\AppData\Local\Microsoft\Microsoft SQL Server\100\Tools\Shell\1033

Any template you add via the template Explorer is only added to you local copy.  When SSMS opens it retains any custom templates that you may have created but it also ensures that all the basic templates are installed.

If you want to remove templates form your local folder on a permanent basis, then you actually have to delete them from the master repository, likewise if you want to ensure the any custom templates you create are never deleted (even by accident) then put them in the master repository and then they will always be copied over to your local folder.

I hope this post has been helpful insight into using templates and that you will start to use templates to stream line some of your development practices.