SQL Server Management Studio (SSMS) is a popular client tool to connect with SQL Server and perform various development and administrative tasks. Beginner developers or DBAs might not be familiar with T-SQL or code for specific tasks. Even for an expert DBA, query templates and shortcuts help immensely to save time and improve productivity. SSMS template explorer is an often-overlooked solution, but it is a useful feature.
SSMS template explorer is a collection of useful query templates. You need to provide the required inputs and execute the query. For example, in a create database template, we only need to specify the new database name.
Prerequisite
In this article, I am using the latest SSMS 18.5 version released on April 07, 2020. You can download it from Microsoft Docs. You can use previous versions of SSMS as well for this article; however, I would recommend using the latest version.
Template Explorer in SSMS
We can launch Template Explorer from the SSMS menu bar. Navigate to View-> Template Explorer.
Alternatively, we can also use a keyboard shortcut Ctrl + Alt + T for this template explorer. It opens the following template browser.
It groups the templates as per their categories in the various folders. It provides templates for both database and analysis services.
In the bottom section, you can see recently used templates. For example, I opened the Create Database template recently, so it shows up here. In this way, you can easily pick the recently used template without going through folders.
Let’s expand the database folder and view the templates inside it.
It shows scripts to create the database, create a database with multiple filegroups, create a database with the filstream filegroup, bring the database online, take the database offline, attach and detach the database. These are the everyday tasks we do in a database.
Let’s say I want to create a database without specifying any additional filegroup. Now, double-click on the Create database.
It contains a script for the drop database if it already exists and creates a database without specifying the data and log file directories. By default, SQL Server creates a copy of the model database and creates a database in the configured default directories.
In the above scripts, we need to specify database name three times. We can either manually enter the value of the variable or use a template value method that we will explore shortly in this article.
Let’s close this template and open another template to create a database with multiple filegroups. It is a lengthy script, and you might feel difficult to enter the variable values. You require to go through each line of code and provide value for the variable. Annoying, Right!
Specify Values for Template Parameters
SSMS solves the problem shown above with a simple configuration. Navigate to Query-> Specify Values for Template Parameters.
You can also use keyboard shortcut key Ctrl+Shift+M.
It opens a pop-up with a list of used parameters, its type, and values. You can also see it highlights all variables with a background color.
Specify values for these parameters in this parameter box. You only need to specify the value for a parameter once, and SSMS automatically replaces the parameter variables with the value specified. This way, we do not need to go through each line of code, look for the variable, and assign value. It also saves time and eliminates human errors.
Click OK, and it replaces the variables with the value we specified in the previous step.
You can run the script directly, and it creates a database for you. It simplified your task, isn’t it! You specified only a few parameters values, and your script is ready.
Let’s take another example of creating a stored procedure using the highlighted template.
Launch this template and view the query with variables. In a stored procedure, we define the variable and its data types such as int, varchar(), char, float.
In the below template, we see that it asks for data types of both variables @param1 and @param2.
Now, launch the specify values for template parameters, as shown below. Here, we see that it defines variable data types. By default, it uses int data type.
We can change the data type as per our requirement. For example, let’s change the data type for both variables to tinyint. It is a text box, and you can write the data type. It should be a valid data type as per the SQL Server version.
Click OK, and you can see changes in the script.
Create a User-defined template
SSMS allows you to define your script templates. It is an excellent way to define your scripts and frequently used scripts such as procedure, function, views. For this demo, I am using the alter stored procedure template code and saving it as a new custom template.
Suppose you want to create a template to alter a stored procedure. Right-click on the Stored Procedure folder and choose to select a new Template. You can create a new folder as well.
It creates a blank template inside the folder. Give it an appropriate name, as shown below.
Right-click on this template and click on Edit. Write the code in the template window as per your requirements.
Save this template using Ctrl + S, and you can use it as other system templates.
You should also explore Analysis Services templates for DAX, DMX, and MDX queries.
Conclusion
SQL Server script templates provide a powerful utility in SQL Server Management Studio (SSMS). We can use it as a repository for frequently used queries with variables. It is easy to use and design for user-defined templates as well. I recommend you to take a look and use them if you find it suitable for your needs.
I am the author of the book "DP-300 Administering Relational Database on Microsoft Azure". I published more than 650 technical articles on MSSQLTips, SQLShack, Quest, CodingSight, and SeveralNines.
I am the creator of one of the biggest free online collections of articles on a single topic, with his 50-part series on SQL Server Always On Availability Groups.
Based on my contribution to the SQL Server community, I have been recognized as the prestigious Best Author of the Year continuously in 2019, 2020, and 2021 (2nd Rank) at SQLShack and the MSSQLTIPS champions award in 2020.
Personal Blog: https://www.dbblogger.com
I am always interested in new challenges so if you need consulting help, reach me at rajendra.gupta16@gmail.com
View all posts by Rajendra Gupta
- Understanding PostgreSQL SUBSTRING function - September 21, 2024
- How to install PostgreSQL on Ubuntu - July 13, 2023
- How to use the CROSSTAB function in PostgreSQL - February 17, 2023