Using The Template Explorer Of SQL Server Management Studio

Sateesh Arveti
8y
4.8k
0
4
100
Article

In this article, we will look into Template explorer of SQL Server Management Studio, how to use it and create our own templates. Templates are similar to code snippets in Visual Studio, it will have code templates for tasks like table, synonym creation etc. We only need to drag-n-drop a template, replace parameters and execute script. It will save time, development effort in writing boiler-plate code. Let’s open SSMS and click on View menu, then Template Explorer [shortcut: Ctrl + Alt + T], it opens below window with pre-defined templates category-wise:

audit

We can expand category and double-click on any script file to use it. Let’s use Create Database template:

It will open Editor with below code:

code

We can replace parameters like database_name manually or use “Specify Values for Template Parameters” dialog [shortcut: Ctrl + Shift + M] from Query menu. This dialog is a grid with three columns. The Parameter and Type columns are read-only and cannot be changed and Value column can be changed.

value

After specifying database name, it will replace parameters with our inputs. Now, we can execute script to create database TestDB. Similarly, we can use other scripts as well without writing boiler-plate code.

Let’s create our own template to rename a database. Right-click on Database node in Template Browser, select New , then Template, name it “Rename Database” and click on Edit to add below script:

code

Here, we defined two parameters source_db_name and dest_db_name enclosed in angle brackets (<>) in the format <parameter_name, data_type, default_value>.

Now, Click Query, then “Specify Values for Template Parameters” for replacing parameter values:

value

After clicking OK, it will replace parameters with its values as shown below:

code

We can execute the script to rename the database. All pre-defined and custom templates will saved under C:\Users\<User Name>\AppData\Roaming\Microsoft\SQL Server Management Studio\<Version>\Templates\Sql\<Category>\.

We can share these templates for others by distributing these SQL scripts.

By using templates, we can save time and development effort by re-using existing code.I am ending the things here, I hope this article will be helpful for all.