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
After that you’ll see something similar to this jump up on the screen, that’s the 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).
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.
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.
I’ve found this to be useful on keeping track of all the utility scripts I’ve picked up along the way.