T-SQL Coding Made Easy with Inbuilt SQL Server Templates
However, if you have an option of getting the well formatted code snippet with the syntax of all the commonly performed T-SQL / MDX / DMX etc. operations right in the query editor window, then nothing like it. I would love such a feature.
Microsoft as always known for offering most powerful and user friendly tools has provided a separate section / explorer in SQL Server Management Studio 2005 / 2008 which contains the templates / code snippets for all the commonly performed operations in T-SQL, MDX, DMX etc. This feature / explorer is called as Template Explorer. Few of you reading this post might already be knowing about this feature, but explaining it in detail for those readers who have not used this feature before.
Following is a snapshot of Template Explorer in SQL Server 2008 Management Studio showing the list of various well formatted code snippets grouped into various categories. Below snapshot shows all the available categories of code snippets for T-SQL operations in SQL Server 2008 with the list of code snippets expanded for commonly performed T-SQL operations on tables.
Template Explorer in SQL Server Management Studio (SSMS) can be accessed using the following ways:
- SSMS Menus – View -> Template Explorer
- SSMS Shortcut – Ctrl + Alt + T
Following screenshots show the comparison of Template Explorer between SQL Server 2005 & SQL Server 2008 for T-SQL / SQL Server, MDX, Mobile / Compact Editions.
Now let us take a look at how to generate the table creation script for a sample table using the Create Table template available in Template Explorer.
Go to template explorer and expand the Tables folder under SQL Server section in Template Explorer as shown in the above figure 1. Now double click on the Create Table template to open it in a new query editor window.
Following code snippet is present in the Create Table template:
Now go to Query -> Specify Values from Template Parameters… in SSMS. This brings up a pop-up window “Specify Values from Template Parameters“. In this window specify the parameters like Column Names, Column Data Type, NULLability, Constraints etc. as shown in the below screenshot.
Now when you click OK, the Table Creation Script for the SampleEmployeeTable gets generated as shown below.
— Create table template
IF Object_id(‘dbo.SampleEmployeeTable’, ‘U’) IS NOT NULL
DROP TABLE dbo.sampleemployeetable
CREATE TABLE dbo.sampleemployeetable
employeeid INT NOT NULL,
employeename NVARCHAR(255) NULL,
employeeaddress NVARCHAR(512) NULL,
CONSTRAINT pk_sampleemployeetable_employeeid PRIMARY KEY (employeeid)
Using Template Explorer we can perform the following tasks:
- Edit the existing templates to include additional details like parameters, fields, comments, formatting etc.
- Create new templates for commonly used scripts in a module / project so that they can be reused by other team members and also across various modules / projects.
- Rename existing folders / templates as per your convenience (Though this is not required 99.9% of the times as all the folders which ship with SQL Server are named appropriately).
- Create new folders / Renaming existing folders to add new or existing templates.
- Re-organizing the templates by moving them across folders.
Following are few of the advantages / benefits of using Template Explorer:
- These templates act as a quick reference for getting the syntax in the absence of locally installed SQL Server Books Online or Access to Internet.
- Saves time required for formatting the T-SQL code since the templates are pre-formatted hence ensures consistency in terms of formatting across various modules / projects.
- We can share commonly used scripts across all the team members working across modules / projects.
Please let me know your opinion about this article / feature by leaving a comment below.