ssms排序代码_SSMS中的代码片段概述-CSDN博客

ssms排序代码

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

本文探讨了SSMS代码段功能及其自定义方法。

介绍 (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.

SSMS是SQL Server可以执行管理和开发任务的流行客户端工具。 Microsoft已从SQL Server安装程序中排除了SQL Server管理工作室。它是一个独立的客户端程序，具有频繁的更新和错误修复。

Current SSMS release: 18.4 (release date: November 04, 2019) 当前的SSMS版本：18.4（发布日期：2019年11月4日）

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.

在文章《 Azure Data Studio中SQL代码片段》中，我们学习了如何在跨平台Azure Data Studio中使用代码片段功能。 SSMS是否具有相同的功能？让我们在本文中进行探索。

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.

连接到SQL Server实例并打开一个新的查询窗口。右键单击查询编辑器，它会为您提供选项列表，如下所示。

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.

单击“在新查询窗口中插入片段”（如上述屏幕截图所示）。或者，按CTRL + K。您收到以下消息，按了CTRL + K。 等待状态栏下方的和弦第二个键 。

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

现在，按CTRL + X，它会根据对象组为您提供一个摘要类别。

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.

假设我们要为我们的演示创建一个SQL表。导航到表格，然后按Enter。它提供了表类别的可用代码段。您可以在下面看到它包含一个片段–为该类别创建一个表。

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
创建主要的XML索引
Create Unique Non-Clustered Indexes
创建唯一的非聚集索引

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

选择基本创建索引，然后按Enter。它提供了以下代码。

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

如果选择存储过程，则会得到以下模板。

Create procedure basic template
创建过程基本模板
Create procedure with CURSOR OUTPUT parameter
使用CURSOR OUTPUT参数创建过程
Create procedure with OUTPUT parameter
使用OUTPUT参数创建过程

环绕功能 (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.

在代码中，我们使用begin ， while和If代码块来定义各种逻辑。我们可以使用带有环绕声的功能来插入Begin，While和If逻辑模板。

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.

您可以在SSMS选项中单击环绕（请参阅第一个屏幕截图）。或者，按CTRL + K，然后按CTRL + S。您可以看到Begin，If和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.

现在，假设我们要在存储过程中进行while循环。该while循环应围绕过程的select语句。

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

用while循环突出显示我们要包围的select语句。

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

现在，按CTRL + K，再按CTRL + S，然后从下拉菜单中选择while循环。

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.

按Enter键，而循环围绕突出显示的选择语句。它还突出显示条件字，该条件字指定您需要配置while循环的条件。

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

在存储过程的while循环中指定条件逻辑，您可以在实例中很好地实现此条件。

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

同样，您可以使用带环绕声选项的If和Begin循环。

代码片段管理器 (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.

或者，按CTRL + K，然后按CTRL + B，以启动它。

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.

在此代码段管理器中，我们查看，添加或删除可用的代码段。您还可以创建一个新的SSMS代码段，并将其导入相应的组中。

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.

您还可以在Microsoft SQL Server Management Studio文件夹中查看代码段的位置。对于SSMS 18，它将所有代码段存储在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.

在Visual Studio或适当的XML编辑器中打开此代码段。

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

让我们在此XML代码中进行以下更改。

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)

将模式从dbo更改为Person，并将列1的数据类型从int更改为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

添加一个新列。指定列的名称– Column_3和数据类型位。我们可以复制粘贴上一列的详细信息，然后重命名该列的属性
Specify the column in create table command, as shown below

在create table命令中指定列，如下所示
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.

现在，让我们在新的查询窗口中检查此代码段。我们看到了这个自定义代码段，如下所示。您可以看到描述创建表-为此，演示了XML中指定的Rajendra 。

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

按Enter键，然后查看此自定义代码段的代码。满足我们上面指定的要求。

为sp_helpdb添加自定义代码段 (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.

让我们创建一个基本代码片段，该片段为SQL Server执行sp_helpdb命令。您可以引用现有代码段的XML。在此，以XML格式定义标题，描述，作者，代码段类型和代码。下面是sp_helpdb系统过程的参考XML。

<?XML version="1.0" encoding="UTF-8"?>
<CodeSnippets xmlns="http://schemas.microsoft.com/VisualStudio/2005/CodeSnippet">
   <CodeSnippet Format="1.0.0">
      <Header>
         <Title>sp_helpdb</Title>
         <Shortcut />
         <Description>It returns output of sp_helpdb</Description>
         <Author>Rajendra Gupta</Author>
         <SnippetTypes>
            <SnippetType>Expansion</SnippetType>
         </SnippetTypes>
      </Header>
      <Snippet>
         <Code Language="SQL"><![CDATA[Exec sp_helpdb]]></Code>
      </Snippet>
   </CodeSnippet>
</CodeSnippets>

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

从SSMS工具箱中启动代码段管理器，然后单击“导入”。浏览到包含代码段文件的目录。

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.

现在，查看我的代码片段文件夹中的代码片段，然后按Enter。

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.

在本文中，我们探讨了SSMS的有用的代码片段功能。它可以帮助您快速，有效地编写查询。对于不熟悉t-SQL语法的初学者也很有用。我们也可以根据您的要求自定义它们。