Tips ‘N’ Tricks – SSMS – Write Queries Faster and Cleaner Using System and User Defined Query Templates

I had blogged about a tip recently on Scripting your actions in SQL Server Management (SSMS) which helps us to easily generate T-SQL Scripts even for those operations for which we do not remember/know the exact the syntax in T-SQL. That definitely is a nice approach, however, there is one more approach which can be used to quickly write-up scripts for most of the common tasks which we perform on a database in our day-to-day work. This approach, however, provides a template and we need to fill in the details to get the script completed for the intended task.

This feature called Template Explorer in SSMS, offers a list of various pre-defined T-SQL Templates for performing majority of the common tasks using T-SQL which we perform in our day-to-day work.

To bring up the Template Explorer, Go to View | Template Explorer or alternatively, you can press Ctrl+Alt+T.

Below screenshot shows a list of all the available Templates categorized into different categories based on the type of object/operation.

Template Explorer in SQL Server Management Studio

For a step by step instructions on using these templates, take a look at this article: T-SQL Coding Made Easy with Inbuilt SQL Server Templates.

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.

What is your favorite way of writing T-SQL Scripts? Let me know by leaving a comment below 🙂

Take a look at the other Tips and Tricks in this Series on Tips, Tricks, Techniques, and Shortcuts to Improve Productivity, and Design and Coding Skills.


About Dattatrey Sindol (Datta)

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

Posted on June 19, 2012, in Code Snippets, Development Tips, Keyboard Shortcuts, Shortcuts, SQL Client Tools, SQL Server, SSMS, SSMS Shortcuts, SSMS Tips, T-SQL, T-SQL Snippets, T-SQL Tips, Tips 'N' Tricks and tagged , , , , , , , , , , , . Bookmark the permalink. 2 Comments.

What are your thoughts?

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

You are commenting using your 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 )

Google+ photo

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

Connecting to %s

%d bloggers like this: