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.
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.
- Tips ‘N’ Tricks – SSMS – Execute Single Query against Multiple Servers
- Tips ‘N’ Tricks – SSMS – Re-arranging the Column Sequence in the Results Pane
- Tips ‘N’ Tricks – SSMS – Save Your Frequently Used Servers and Databases
- Tips ‘N’ Tricks – SSMS – Easy way to Learn Keyboard Shortcuts by Enabling ScreenTips
- Tips ‘N’ Tricks – SSMS – View the Column List, Data Types, and Null Option in Query Tooltips
- Tips ‘N’ Tricks – SSMS – Quickest way to Get Row Count of All Tables from a SQL Server Database
- Tips ‘N’ Tricks – SSMS – Quickest way to SELECT and Query All Columns from a Table in SQL Server
- Tips ‘N’ Tricks – SSMS – Generate Script for Multiple Select Objects in One-Go
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 Best Practices, Databases, Formatting, Keyboard Shortcuts, Shortcuts, SQL, SQL Server, SQL Server Management Studio, SSMS, Templates, Tips and Tricks, Transact-SQL. Bookmark the permalink. 2 Comments.