The History and The Problem

Microsoft came out with this cool idea in the old SQL Server 2000 Query Analyzer for administrative code samples called Templates.  This is a collection of sample code snippets available within Query Analyzer in the Template Browser.  This collection of code snippets are actually individual files and you may add your own custom scripts to this collection.  It's a really handy way to keep useful scripts at hand and can be used by either dragging and dropping a template on to a query window or by double clicking to open a new database connection with the template script loaded.  One more option exists to change the template code by right clicking and selecting Edit from the popu context menu.

1 - Query Analyzer Template Tab

It seemed like a great idea to me, so I diligently started putting all my SQL code in this newfound repository for easy access within Query Analyzer.  I loved the idea so much after adding a few scripts in the Template Browser user interface I wanted to bulk add many of my own scripts and use this new repository exclusively.    Where are they stored anyway? Read on because the answer to that question varies depending on the version of SQL Server. 

In the options for Query Analyzer under the General tab I found the setting for the template location. The default location is C:\Program Files\Microsoft SQL Server\80\Tools\Templates\SQL Query Analyzer.  The files have extensions of *.tsql and must be named this way to show up in Query Analyzer.  I elected to categorize my scripts into subdirectories based upon functionality.  This follows the convention used in the Template Browser.  Each directory category shows up as a node in the Template Browser window pane.  Yes, I'm a T-SQL pack rat, so I had hundreds of SQL files that I had to categorize. Once that was done I just copied them to the repository where SQL Server stores all of it's templates.  All of my categorized custom scripts showed up in the template browser after I copied them to the default template location and restarted Query Analyer.

When SQL Server 2005 was released I wanted to continue using the SQL Server Management Studio (SSMS) template repository exactly as I had in Query Analyzer.  knew that I would have to migrate my templates to a new directory location or can they be shared?  I first decided to find where to put all my custom template code and then think about the sharing.

The first thing noticed in SQL Server 2005 SSMS is that theTemplate Explorer is no longer displayed by default.   To display the Template Browser pane, go to the View menu and choose Template Explorer.  A dockable panel will open showing all the default templates that come with SSMS.  One thing I noticed was that it took way too long just to display the tree nodes in this new version of Template Explorer but I was unsure why that could be.  Looking in the options dialog for SQL 2005 was not fruitful either because the template location is no longer shown as an option.

Here is a screen shot of my SQL 2008 Template Explorer that I have docked along with Registered Servers and Object Explorer.

During my research I discovered that custom templates are stored under your AppData folder in your Windows user directory.  Here is the exact path: C:\Users\<User>\AppData\Roaming\Microsoft\Microsoft SQL Server\100\Tools\Shell\Templates\Sql on my machine.  If you take a look in there you will see that not only your custom templates are located here but also all the standard ones that come with SSMS.  The other location for the templates is under C:\Program Files (x86)\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\SqlWorkbenchProjectItems\Sql.  This location contains only the standard templates installed with SSMS.  Another finding was that the tsql file extension is no longer used for templates but rather .sql instead, so renaming was necessary.

Why are the standard templates in both locations?  Well, the idea (which is a very questionable "feature" if you ask me) is that each user on a particular machine will have their own copy of all templates in their user directory.  So when the Template Explorer is accessed in SSMS the contents of the Program Files version of the templates is copied over the ones located at the AppData user directory.  This explains the delay in populating the Template Explorer tree nodes as well. Unfortunately this makes it very difficult to share a common repository with team members on different machines.  Our script library had grown quite a bit as well in the years between SQL 2000 and the release of SQL 2005 and we were really hoping that the templates could be easily shared amongst us.  We had some success but it just was not as simple as I would have liked.

A Pretty Good Solution

Windows 7 introduced a new shell command called mklink.  A similar command has been available to Unix and variants like Linux for many years.

MKLINK [[/D] | [/H] | [/J]] Link Target

       /D      Creates a directory symbolic link.  Default is a file

               symbolic link.

       /H      Creates a hard link instead of a symbolic link.

       /J      Creates a Directory Junction.

       Link    specifies the new symbolic link name.

       Target  specifies the path (relative or absolute) that the new link refers to.

It's called a symbolic link, or sometimes referred to as soft link, and acts similarly to a windows shortcut.  But there is a big difference.  When you navigate through a symbolic link from a target directory the contents of the linked directory will show up as if contained in the target.  For instance, I created a symbolic link from my source code repository to my AppData user directory where the templates are stored with the following commands in a cmd window (lines are not wrapped in the command prompt window but are wrapped here for display only):

Change to the User AppData directory for templates

cd C:\Users\Carl\AppData\Roaming\Microsoft\Microsoft SQL Server\100\Tools\Shell\Templates

Rename the existing Sql.  You will get an error if the target folder already exists.

ren Sql Sql_Old

Create the directory symbolic link to your desired repository.

mklink /D Sql C:\ss\Internal\Code\TSQL\SSMS_Templates

If all is successful you will see

"symbolic link created for Sql <<===>> C:\ss\Internal\Code\TSQL\SSMS_Templates"

Summary

 Now when the AppData directory is accessed the contents of our shared code repository are shown.  This worked perfectly with SSMS for SQL 2005, 2008 and 2008 R2 and is a breeze to setup on a new machine. Also, I used the symbolic link technique to point my SQL 2005 and SQL 2008 R2 template directories to the same location which is really nice for me.  UNfortunately the shared approach does not work for SQL Server 2000 because of the different file extension used for templates.

I'm sure there are several other techniques for accomplishing this same thing and I would be very interested to hear how you may have tackled a similar issue.

All of my templates are now in a location that is accessible by our DBA team and DBA life has become good once again.