T-SQL Coding Made Easy with Inbuilt SQL Server Templates

I have seen many people, even though they know what function / clause / keyword to be used for a particular T-SQL / Other type of query / operation, they don’t remember the exact syntax [Including me at times :-)]. From my perspective as long as we know what keywords / functions / clauses need to be used for performing a specific operation using T-SQL / Other language (like MDX, DMX etc), we can always get the syntax from various sources like msdn, SQL Server Books Online (Local / Online version) etc. and get the task done.

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
— =========================================
USE mytechnobook

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.


About Dattatrey Sindol (Datta)

Datta is a Microsoft BI Enthusiast, passionate developer, and a blogger. View Full Profile

Posted on June 12, 2010, in SQL Server, SSMS, T-SQL. Bookmark the permalink. 2 Comments.

What are your thoughts?

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s