Write Queries Faster and Cleaner Using System and User Defined Query Templates

---For Syntax

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.

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.