This article explores the SSMS Code Snippet feature and ways to customize it.
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.
- Stored procedure
- User-defined data type
- User-defined table type
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.
<?XML version="1.0" encoding="UTF-8"?>
<Description>It returns output of sp_helpdb</Description>
<Code Language="SQL"><![CDATA[Exec sp_helpdb]]></Code>
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.
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.
- DTU and vCore based models for Azure SQL Databases - September 27, 2021
- Custom Azure Policy definitions for Azure SQL Databases backup retention periods compliance - September 22, 2021
- Identify and remediate non-compliant Azure SQL Databases for auditing on SQL Server - September 16, 2021