I have used several different methods for collecting SQL scripts (both that I have written, and have borrowed from others) to reuse.  A few that come to mind include: 1. saving the scripts in individual .sql files somewhere on my hard drive, 2. storing the scripts as an individual page in Microsoft Onenote. 3. saving scripts right in SSMS Template Explorer.

While I still have a collection of scripts in all 3 places for various purposes, I have been moving more and more towards using template explorer.
The biggest reason for my change  is because whenever I need to run a script against a database server I am already opening up SSMS.  It is then easier to open up template explorer than it is to then click on File->Open, and then browse for a specific script.

Lets get started

1. Open SSMS
2. Open the Template explorer by either using the key combination CTRL + ALT + T  (my preferred method), or clicking on View->Template Explorer.
3. The template explorer will then open on the right hand dock in SSMS (kinda like how it shows up in my screen shot here!).

From here you can expand any of the categories to see a list of applicable scripts.  Microsoft gives us a decent collection of templates to get us started and you can add your own scripts to the collection (covered in a future blog post).  Find a script template that you want to use and double click it.  SSMS will open that template up as a new query in the main window.  WARNING: Make sure that the script window is connected to the appropriate server\instance before executing any scripts. (not that I have ever done that….)

For this example, I expanded the Backup category, and then double clicked on “Backup Database”

Now wait a minute, there’s a lot of funky stuff there.  “BACKUP DATABASE” won’t run.

Here comes the magic of Template Explorer.  Press CTRL + SHIFT + M on your keyboard.  Behold the Specify Values prompt box.

Fill in the database name, and the file location where you want the backup saved to and press OK.  Like Magic, the template place markers are removed from the script and replaced with the values that you specified a moment ago.  The script is now ready to run as long as you double check to make sure you are running it against the appropriate server\instance.

.

A couple gotchas

• All of the Template Explorer scripts are saved on my PC in:
  C:\Users\<user profile>\AppData\Roaming\Microsoft\Microsoft SQL Server\100\Tools\Shell\Templates\Sql
  This is running SSMS 2008 R2 on Windows 7.
• This save path for the templates does not get backed up on my local PC.  It also does not travel if I use SSMS on another PC.
• So far my solution has been to use a Remote Desktop Connection back to my main work PC if I am using another PC in the office.
• Every now and then I will manually copy the directory up to a network location that does get backed up so that I can get my templates back if my PC dies.  Does anyone have a better solution?
• If you delete a template or folder that Microsoft provided, it will be recreated whenever you restart SSMS.  I found that it is best to leave the Microsoft provided templates alone and add my own.

Coming soon in a future post, making your own template. [EDIT: here is part 2]