One of the things I really like about SQL Server Management Studio (SSMS) is the ability to use templates to help standardize and automate the creation of SQL code.
This works by including special text in your template document, much like mail merge in the old days of word processing. Pressing Ctrl-Shift-M opens a dialog allowing you to replace the text from the template with your specific value.
The template tags you create have three parts.
- Parameter
- Type
- Value
Property and Object are used to help guide the user in their selection for a value. Pressing Ctro-Shift-M opens a dialog consisting of all the tags defined in the template using a syntax like the following example:
This tag will show up in the dialog with the property = schema_name, the type = sysname and the value = dbo. Only the value portion of a template tag may be edited by the user. The Property and Type data are deleted, and the value is replaced with the value entered by the user.
In the example above, the template parameter has a default value of dbo. You can create a template property without a default value or prompt by leaving any of the three values empty. You need to have at least one value for it to be meaningful when displayed in the input dialog. So, the same example above without a default value would be
You can put anything you want into the three properties. I have put options in the type property for some of my templates like the example that follows. This example shows dbo | service | hr under the type heading in the dialog.
SQL Server ships with a lot of pre-defined templates. If you press Ctrl – Alt – T or select Template Explorer from the View menu, an explorer is opened with all the canned templates available. You can modify those templates, or create your own.
Templates are very useful if you find yourself typing the same thing over and over. Another benefit is that they can shape your SQL syntax so you are consistent in the way you write everything. A template with a common header could be helpful. Maybe you want to put some standardized copyright information or the like.
Cheers,
Ben