Rajendra Gupta
sp_helpdb

An overview of Code Snippets in SSMS

February 24, 2020 by

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.

Download SSMS

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.

SSMS options

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.

 Insert Snippets

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

snippets category

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.

a SQL table

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

create table definition

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.

Select Create Index basic

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

Create procedure basic template

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.

Surround with feature

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

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

procedure basic template

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

procedure name and parameters

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.

Highlight the select statement

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

press CTRL+K followed by CTRL+S

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.

highlights condition

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

Specify condition logic

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.

Code Snippets Manager

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

Code Snippets Manager category

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.

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.

customize this create table snippet

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

XML code

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

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

    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)

    Change the schema

  • 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

    Add a new column

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

    Specify the column

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

    Save the new code snippet

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.

custom snippet

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

view the code for this custom snippet

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.

Browse directory

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

validates the code definition

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

snippet property

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

my code snippet folder

It prints the query specified in the code snippet.

prints the query

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.

Rajendra Gupta
1,174 Views