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.

Advertisements

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:

WordPress.com Logo

You are commenting using your WordPress.com 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: