Rajendra Gupta
Specify parameters value

Script templates in SQL Server Management Studio (SSMS)

April 27, 2020 by

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.

SSMS latest version

Template Explorer in SSMS

We can launch Template Explorer from the SSMS menu bar. Navigate to View-> Template Explorer.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.

Template Browser

Let’s expand the database folder and view the templates inside it.

View templates

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.

View Parameters

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!

Create a database with multiple filegroups

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.

Specify Values for Template Parameters.

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.

View parameters

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.

Specify parameters value

Click OK, and it replaces the variables with the value we specified in the previous step.

View modified script

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.

Stored procedure

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.

Variable and data types

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.

Change 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.

View modified data type

Click OK, and you can see changes in the script. Verify changes

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.

Create a User-defined template

It creates a blank template inside the folder. Give it an appropriate name, as shown below.

Rename template

Right-click on this template and click on Edit. Write the code in the template window as per your requirements.

Edit user-defined template

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.

Analysis Services templates

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.

Rajendra Gupta
Latest posts by Rajendra Gupta (see all)
SQL Server Management Studio (SSMS)

About Rajendra Gupta

As an MCSA certified and Microsoft Certified Trainer in Gurgaon, India, with 13 years of experience, Rajendra works for a variety of large companies focusing on performance optimization, monitoring, high availability, and disaster recovery strategies and implementation. He is the author of hundreds of authoritative articles on SQL Server, Azure, MySQL, Linux, Power BI, Performance tuning, AWS/Amazon RDS, Git, and related technologies that have been viewed by over 10m readers to date. He is 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 his contribution to the SQL Server community, he has been recognized with various awards including the prestigious “Best author of the year" continuously in 2020 and 2021 at SQLShack. Raj is always interested in new challenges so if you need consulting help on any subject covered in his writings, he can be reached at rajendra.gupta16@gmail.com View all posts by Rajendra Gupta

8,465 Views