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.
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.
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.
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.
- Azure Analysis Services and Power BI Live connections - April 14, 2021
- An overview of Power BI data models - April 12, 2021
- Capturing deadlocks on AWS RDS SQL Server databases - April 7, 2021