Management Studio Query Templates

Did you know that SQL Server Management Studio comes with a number of ready made templates for writing queries? If not, don’t worry, you’re not alone. Lot of people I’ve talked with aren’t familiar with these, even though they’re not all that well hidden secret.

Let’s take a look at where they are and how they can be used for making your life just a bit easier.

First of all, the Template Explorer where these can be found, isn’t on by default in Management Studio. You can access it from going to View and selecting Template Explorer or just press CTRL+ALT+T
View menu
After that you’ll see something similar to this jump up on the screen, that’s the Template Explorer.
Template Explorer
Using these templates is equally simple to finding them, just choose the one you want to use and double click it or drag it into previously opened Query window. Lets look at a quick example on how to use Backup Database template.
We’ll first go into folder called Backup, open it and double click Backup Database which will open up the template (below).
Backup DB Template
Backup Template
After that all you need to do is to edit in correct database name, paths etc and you’re ready. This isn’t just a great time saving feature as it also means that you don’t need memorize every single command SQL Server has (even though it’s pretty cool and impressive if you do, maybe, in some circles).

There’s also one neat feature you should know about. You can add your own templates into the Template Explorer!

I usually create my own folder there for my own scripts. You can do this by right clicking the SQL Server Templates folder and choosing a New and Folder.
My own templates
You then proceed creating the actual templates pretty much in the exactly same way you created the folder.
Right click the folder you just created and choose New and Template. You’ll be prompted for the name, type it and hit Enter. Once that is done, you can right click it and choose Edit to add some code into it.
Editing my cool script
I’ve found this to be useful on keeping track of all the utility scripts I’ve picked up along the way.

Author: Mika Sutinen

Hi, My name is Mika Sutinen and I'm a Senior Database Administrator for a company called Tieto. I've been working in IT-industry for two decades and I've spend most of my career working with healthcare information systems. I've worked with SQL Server for most of my career, starting with version 6.5 a long, long time ago. My other interests are high availability, everything related to performance (testing, monitoring, etc), Windows operating systems and I'm currently learning more about Azure.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s