Problem
SQL Server has become a very popular enterprise database system for mission critical business applications. As SQL Server professionals, we use several scripts on each to perform DBA and development duties. It’s time consuming to repeatedly type long T-SQL statements every time you need to do build code especially for frequent activities. Also, it is not entirely reliable to keep and manage numerous scripts. SQL Serve offers a very useful feature called Template Explorer in SQL Server Management Studio (SSMS) to address this issue. Read this tutorial to understand and learn how to use script templates in SSMS.
Solution
Microsoft has developed Template Explorer to manage script templates in SQL Server Management Studio. There are hundreds of built-in script templates already in Template Explorer which can be used to fulfill various requirements. Additionally, you can create your new script template for activities that you perform on a frequent basis in Template Explorer. In this tutorial, I will demonstrate various aspects and functionality within Template Explorer.
Template Explorer in SQL Server Management Studio
Template Explorer can be used to save time and effort in writing the same scripts repeatedly. You can use the explorer to access several built-in script templates and if needed create your own custom script templates and Template Explorer can be used to manage your script template repositories.
Template Explorer provides script templates for two SQL Server services:
- SQL Server Database Engine
- SQL Server Analysis Services
We can also get various script templates for Azure SQL Databases under the SQL Server Database Engine tab and I will demonstrate what script templates are available.
All of the script templates are divided into two tabs: SQL Server Database Engine or SQL Server Analysis Services. Each tab is further categorized with several folder trees where all script templates are created. Each script template falls under their respective folder name that is related to the purpose of the script. It is recommended that you also structure your custom scripts in the same manner.
We can also edit existing script templates in the Template Browser by choosing the edit option when right clicking on a script template. Then the query will open in an editor where you can make your changes to the template and then you can save the code and close the window.
You can access DBA related script templates and use them as needed. If you are a Developer then you can use templates to create, modify and delete various database objects like stored procedures, tables, views, triggers, etc. You can also create your customized script template inside Template Explorer.
Launch and Explore Template Explorer in SQL Server Management Studio
Let’s launch Template Explorer in SQL Server Management Studio. Click on the “Template Explorer” option under the “View” menu in SQL Server Management Studio as shown below.
Once you click on the Template Explorer, an new window will load on the right side in SQL Server Management Studio named “Template Explorer” as shown below. You will see various categories in the Template Explorer with their parent folder under which the script templates reside. You can also see all recently used script templates in the bottom section of the template browser as shown below. There are no recently used scripts shown below, as I am using this functionality for the first time in this demo.
All the script templates are stored in folders corresponding to the SQL Server database engine service. This tab is denoted by the blue arrow in the above image. Similarly, all folders and script templates for SQL Server Analysis Services can be accessed by using the button shown below with the blue arrow.
Use a Script Template as Demo from Template Explorer in SQL Server Management Studio
We will explore script templates from specific folders to understand how we can use them. I switched back to the SQL Server Database Engine tab to access all Database related script templates and have expanded the folder to see the scripts as shown in the image below. There are three ways to open a script. First is to double click on a script template, second is to right click on the script template and choose the Open option and third is to drag and drop the script template into a query editor.
I double clicked on the “Take Database Offline” script template as shown below. You can see a Query Window has loaded the T-SQL statements in the below image and now we can change this script as needed. For example, we need to pass the database name that needs to be taken offline.
If you are not sure of all of the options, parameters and strings you need to change in the template then you can use the “Replace Template Parameters” option to complete these values.
I changed the database name to AdventureWorks for the template and executed the T-SQL statement as shown in the image below.
Let’s validate the script by running sp_helpdb in a new Query Window or by refreshing the database folder in Object Explorer of SQL Server Management Studio. You can see there is no output for the sp_helpdb command and the AdventureWorks database is in an Offline state in SSMS as shown below.
Now we will use another script template “Bring Database Online”. I opened this template in a Query Window and updated it accordingly.
I executed the below T-SQL statement.
Again, we will run sp_helpdb and refresh the database folder in Object Explorer in SSMS. You can see sp_helpdb shows the database is online and SSMS also displays that the database is online.
You can use various script templates in the same manner to fulfill your day-to-day requirements with the help of Template Explorer.
Use Script Templates from Template Explorer for Various Needs
Let me expand another folder related to SQL Server Change Data Capture (CDC) where you will find both Administration and Configuration scripts.
Below is script template for running backups for CDC enabled databases. If you are not sure about all the options and parameters, you need to change before running this script then you can use the “Replace Template Parameter” option to change them. You can get this option by clicking on “Specify Values for Template Parameters” from the “Query” menu.
Template Explorer is also very useful for Database Developers to access development related script templates to create, modify or drop any database object as shown below.
I have expanded the “Stored Procedure” folder to see all script templates related to CREATE, ALTER and DROP statements.
Below is sample ALTER PROCEDURE code. If you are not sure what parameters to change then use the “Replace Template Parameter” window under the “Query” menu.
Azure SQL Database Script Templates
Template Browser also has script templates for Azure SQL Database as shown below.
I have expanded some of the category folders to see the script templates for Azure SQL Database. There are less script templates for Azure SQL Database compared to SQL Server databases.
Create Custom Script Templates in Template Explorer
Template Explorer provides an option to customize existing script templates as well as create your own script templates and folders based on different categories. To begin, right click on the parent node “SQL Server Templates” and then click on the “New” option. This will display two more options to either create a folder or create a template as shown below, then name accordingly.
If you want to create a new template inside an existing folder then you need to right click on the folder name and choose New followed by the Template option.
Next Steps
- SQL Server Management Studio Template Explorer is very useful to store all your scripts in one place. Create your own templates for various frequent running activities.
- Read more articles on SQL Server:
- Read more articles about SQL Server Management Studio
- Explore more knowledge on SQL Server Database Administration Tips
