This article explores the SSMS Code Snippet feature and ways to customize it.

Introduction

SSMS is a popular client tool for SQL Server to do both administrative and development tasks. Microsoft has excluded the SQL Server management studio from the SQL Server setup. It is a standalone client program with frequent updates and bug-fixes.

• Current SSMS release: 18.4 (release date: November 04, 2019)

In the article SQL Code Snippets in Azure Data Studio, we learned using the code snippets feature in cross-platform Azure Data Studio. Do we have the same functionality with SSMS? Let’s explore in this article.

Connect to SQL Server instance and open a new query window. Right-click on the query editor, and it gives you a list of options, as shown below.

Have you explored the highlighted options- Insert Snippet and Surround with? If not, follow the article to understand it.

Insert Code Snippets

We use code snippets for quickly writing queries without any syntax errors. It is like a code template that you can customize as per your requirement.

Click on the Insert Snippets in a new query window ( as per the above screenshot). Alternatively, press CTRL+K. You get the following message CTRL+K was pressed. Waiting for the second key of the chord below the status bar.

Now, press CTRL+X, and it gives you a snippets category as per the object group.

These snippet categories are as follows.

• Function
• Index
• Login
• Role
• Schema
• Stored procedure
• Synonym
• Table
• Trigger
• User
• User-defined data type
• User-defined table type
• User-defined
• View

Suppose we want to create a SQL table for our demonstration. Navigate to the table and press enter. It gives available code snippets for the table category. You can see below that it contains a single snippet – Create a table for this category.

Click on the create table, and it populates create table definition for you.

It also highlights the code that you can change for creating a table. For example, we can change the table name, column name, and data types.

Similarly, for the index category, we get three code snippets. You can navigate to the individual snippet, and it gives a brief introduction as well.

• Create Index basic
• Create primary XML index
• Create Unique Non-Clustered Indexes

Select Create Index basic and press Enter. It gives the following code for it.

If we choose the stored procedure, we get the following templates.

• Create procedure basic template
• Create procedure with CURSOR OUTPUT parameter
• Create procedure with OUTPUT parameter

Surround with feature

In the code, we use begin, while, and If code blocks for defining various logics. We can use surround with a feature for inserting templates of Begin, While and If logic.

You can either click on the surround within SSMS options( refer to the first screenshot). Alternatively, press CTRL+K followed by CTRL+S. You can see templates for Begin, If and While.

We can use these in combination with the code snippets explained above.

For example, first, open a stored procedure basic template.

Make the required changes, such as procedure name and parameters. We can see below the modified code.

Now, suppose we want a while loop in the stored procedure. This while loop should surround the select statement of the procedure.

Highlight the select statement that we want to surround with a while loop.

Now, press CTRL+K followed by CTRL+S, and chose while loop from the drop-down.

Press Enter and while loop surrounds the highlighted select statement. It also highlights condition word that specifies you require configuration of condition for a while loop.

Specify condition logic in the while loop of the stored procedure, and you are good to implement this in your instance.

Similarly, you can use If and Begin loops using surround with option.

Code Snippets Manager

We have code snippets manager to manage the available code snippets. In the menu bar, navigate to Tools and click on Code Snippets Manager.

Alternatively, press CTRL+K followed by CTRL+B, to launch it.

In this code snippets manager, we a view, add or remove available code snippets. You can also create a new SSMS code snippet and import it under the respective group.

Click on required code snippet, and it shows brief information about snippet.

You can also view the location of code snippets in the Microsoft SQL Server Management Studio folder. For SSMS 18, it stores all snippets in C:\Program Files (x86)\Microsoft SQL Server Management Studio 18\Common7\IDE\SQL\Snippets\1033\Table\Create Table.snippet.

Suppose we want to customize this create table snippet. To make the changes, go to the snippet directory and create a copy of the required snippet. We can directly make changes as well, but it is always better to make changes in a copy and later import with a different name.

Open this snippet in Visual Studio or an appropriate XML editor.

Let’s make the following changes in this XML code.

• Modify title, description, description, and author of the new code snippet

  

• Change the schema from dbo to Person and modify the data type of column 1 from int to varchar(50)

  

• Add a new column. Specify the name of a column – Column_3 and data type bit. We can copy-paste the previous column details and rename the column properties

  

• Specify the column in create table command, as shown below

  

• Save the new code snippet and give it an appropriate name

  

Now, let’s check this code snippet in a new query window. We see this custom snippet, as shown below. You can see description Create a table- Demo Rajendra for this as specified in XML.

Press Enter and view the code for this custom snippet. It satisfies our requirements specified above.

Add a custom code snippet for sp_helpdb

Let’s create a basic code snippet that executes sp_helpdb commands for SQL Server. You can reference XML of existing code snippets. Here, defines the title, description, author, snippet type, and code in XML format. Below is the reference XML for the sp_helpdb system procedure.

Launch code snippet manager from SSMS toolbox and click on Import. Browse to a directory that contains a code snippet file.

It validates the code definition and saves it in the “my code snippets folder”.

Once the import is done, we can view its property as shown below.

Now, view the code snippet in my code snippet folder and press Enter on it.

It prints the query specified in the code snippet.

Conclusion

In this article, we explored the useful code snippets feature of SSMS. It helps you write queries quickly, effectively. It is useful for beginner also those are not familiar with t-SQL syntax. We can also customize them as per your requirements.

• Author
• Recent Posts

Rajendra Gupta

Hi! I am Rajendra Gupta, Database Specialist and Architect, helping organizations implement Microsoft SQL Server, Azure, Couchbase, AWS solutions fast and efficiently, fix related issues, and Performance Tuning with over 14 years of experience.

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

Latest posts by Rajendra Gupta (see all)

• 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