SQL Server Management Studio (SSMS) comes with a number of code templates. A template is some shell TSQL code that can be used to jump start your TSQL code writing. You can also be more productive by creating code that will be syntactically correct the first time around. Another advantage of using templates to create your TSQL code is you will not need to look up the syntax for a TSQL command you do not use that frequently, thus saving you time when you are writing your TSQL code.
Read: How to find the Worst Performing TSQL Code.
Templates have been around since SQL Server 2000. In SQL Server 2005 the team at Microsoft included the Template Explorer with SSMS. You can bring up a list of templates in a “Template Browser” window by using the Ctrl-Alt-T shortcut or using the Template Explore menu option under the “View” item on the SSMS main menu.
When the Template Browser window comes up it will look like the following image:
As you can see, there are templates for creating scripts related to backups, databases, and endpoints to name a few. To see all of the templates, use the scroll on the right of the Template Browser menu.
If you want to use one of these templates to build your TSQL code, just expand the high-level item you are interested in and then click on the template you would like to use. Below is a screenshot of some TSQL code that was generated when I clicked on the “Backup Database” template item:
You may notice that this TSQL code has parameters. Parameters are identified by angle brackets (<…>) in the TSQL code above. You can easily replace these parameters using the parameter menu, which can be brought up using the Ctrl+Alt+M shortcut. When you use that shortcut, the following menu is displayed:
As you can see, the template I used has 2 different parameters; one named “Database_Name” and the other named “Backup_Path”.
In order to replace these parameters with the appropriate values, all you need to do is type over the default value that is displayed under the “Value” column for each of the parameters you want to change. Once all the parameters are changed, just click the “Ok” button. By doing this, the values entered are then used to replace the default parameter text in the query window.
Using templates can help you to quickly write usable TSQL code for a specific purpose. That code could be a simple create index statement or could be some really complicated code. Next time you want to write some TSQL code to perform a task, check and see if there is a template to help you jumpstart your TSQL code writing.
