Monday, February 7, 2011

Template Explorer in SQL Server

Although this is not much popular among DBEs, it is one of the facilities should be used. The Template Explorer gives us various type of templates for constructing objects. For an example, if you need to create a database using T-SQL, the template of the CREATE DATABASE can be taken from the Template Explorer and altered as you need. This definitely speeds up coding though it discourages remembering syntax.


The Template Explorer can be opened using View –> Template Explorer (or Ctrl+Alt+T) menu. Templates are grouped into three: SQL Server, Analysis Server, SQL Server Compact. Templates in a group are categorized too. For an example, if you want to create a database with multiple file groups, CREATE DATABASE ON MULTIPLE FILEGROUPS in DATABASE  category under SQL SERVER group can be used.

Template can be brought into query window by dragging and dropping or double-clicking. Once placed, need to replace parameter values with your values. Although you can manually do it, easiest way is, using Specify Values for Template Parameters window. It can be accessed using Query –> Specify Values for Template Parameters menu.

You can create your own templates and have them in Template Explorer. Start it with creating a folder or add new ones in given folders (categories). This facility is open through right-clicking on the folder in Template Explorer and selecting New. One thing you need to remember is the way of adding parameters. Parameters should be enclosed with “<” and “>” and should contain three parts: Parameter name, type, value. You can have the same for both Parameter name and value, allowing user to replace the “value” with their value. For an example, parameter for database name would be: <Database_Name, sysname, Database_Name>.

No comments: