SQL Server Management Studio中SQL代码段

Snippets are a great productivity feature to speed up typing of repetitive T-SQL. Snippets were introduced in SQL Server 2012 as part of SQL Server Management Studio (SSMS).

代码片段是一项强大的生产力功能，可加快重复性T-SQL的键入速度。作为SQL Server Management Studio（SSMS）的一部分，SQL Server 2012中引入了代码片段。

Snippets are XML templates with predefined fields and values that render into T-SQL, when processed by the query window.

代码段是具有预定义字段和值的XML模板，当查询窗口对其进行处理时，这些字段和值会呈现为T-SQL。

In the example below is the results of the Create Trigger snippet that inserts T-SQL required to create a trigger in the query window at the current cursor position. The highlighted elements are placeholders that are replaced with values that the user inputs:

在下面的示例中是创建触发器代码段的结果，该代码段插入了在查询窗口中当前光标位置创建触发器所需的T-SQL。高亮显示的元素是占位符，已被用户输入的值替换：

There are three categories of SQL snippets:

SQL片段分为三类：

Default snippets
These SQL snippets come, out-of-the-box with SSMS and they are placed under folders based on category (e.g. Table, Function, Index).

默认片段
这些SQL代码段随SSMS一起提供，并且根据类别（例如，表，函数，索引）放置在文件夹下。
Surround snippets
These three snippets, found in the Function folder, help to implement code structure like Begin End, While, and If around selected text in the query window.

环绕片段
位于Function文件夹中的这三个代码段有助于实现诸如Begin End ， While ，如果在查询窗口中选中的文本周围。
Custom snippets
Users can create their own custom snippets. Here is an example of the “plumbing” underneath a custom SQL snippet.

…and here how it looks when is initiated in the query window.

自定义片段
用户可以创建自己的自定义片段。这是自定义SQL代码段下的“管道”示例。

…以及在查询窗口中启动时的外观。

By default snippets for SSMS 2016 are found under this path:

默认情况下，在以下路径中找到SSMS 2016的代码段：

C:\Program Files (x86)\Microsoft SQL Server\130\Tools\Binn\ManagementStudio\SQL\Snippets\1033\

C：\ Program Files（x86）\ Microsoft SQL Server \ 130 \ Tools \ Binn \ ManagementStudio \ SQL \ Snippets \ 1033 \

The location of SQL snippets depends on the version of the SSMS that is used. So if SSMS 2014 is used, the default path on which there snippets are: C:\Program Files (x86)\Microsoft SQL Server\120\Tools\Binn\ManagementStudio\SQL\Snippets\1033\

SQL代码段的位置取决于所使用的SSMS的版本。因此，如果使用SSMS 2014，则摘要所在的默认路径为：C：\ Program Files（x86）\ Microsoft SQL Server \ 120 \ Tools \ Binn \ ManagementStudio \ SQL \ Snippets \ 1033 \

The only difference is in the name of the folder that come after the Microsoft SQL Server folder (e.g. folder 130 for SSMS 2016, 120 for SSMS 2014.)

唯一的区别在于Microsoft SQL Server文件夹之后的文件夹名称（例如，SSMS 2016的文件夹130，SSMS 2014的文件夹120）。

To learn more about these folders please visit this link.

要了解有关这些文件夹的更多信息，请访问此链接。

To save a SQL snippet the .snippet extension must be used

要保存SQL代码段，必须使用.snippet扩展名

插入内置代码段 (Inserting a built-in snippet )

There are three ways to insert a built-in SQL snippet that comes with SSMS in the query window:

有三种方法可以在查询窗口中插入SSMS随附的内置SQL代码段：

In the SSMS main menu under the Edit menu, choose the IntelliSense option and select the Insert Snippet command:

在“ 编辑”菜单下的“ SSMS”主菜单中，选择“ 智能感知”选项，然后选择“ 插入代码段”命令：
Right-click in the query window will show popup menu and from the list choose the Insert Snippet command:

在查询窗口中单击鼠标右键将显示弹出菜单，然后从列表中选择“ 插入代码段”命令：
Using the combination of keyboard shortcut Ctrl+K followed by Ctrl+X

结合使用键盘快捷键Ctrl + K和Ctrl + X

Select a category e.g. Function to see all available snippets under that category:

选择一个类别，例如“功能”以查看该类别下的所有可用片段：

Double click on the desired snippet from the list will insert code snippet in the query window with highlighted placeholders that need to be modified:

双击列表中所需的代码段，将在查询窗口中插入带有需要修改的突出显示占位符的代码段：

When a SQL snippet is in the query window, the position of the cursor is in the first highlighted placeholder (e.g. dbo). Enter a value and press the Tab key the cursor position will switch to the next highlighted placeholder (e.g. FunctionName) Inserting a surround SQL snippet

当SQL代码段位于查询窗口中时，光标的位置位于第一个突出显示的占位符（例如dbo）中。输入一个值，然后按Tab键，光标位置将切换到下一个突出显示的占位符（例如FunctionName）。 插入环绕SQL代码段

插入环绕SQL代码段 (Inserting a surround SQL snippet)

There are three ways to invoke a surround SQL snippet in query window. To start, first select the code that want to be surrounded in the query window and then use one of the three ways for invoking:

有三种方法可以在查询窗口中调用环绕SQL代码段。首先，请先选择要在查询窗口中包含的代码，然后使用以下三种方式之一进行调用：

From the SSMS main menu, click the Edit menu and from the IntelliSense option select the Surround With command:

从SSMS主菜单中，单击“ 编辑”菜单，然后从“ IntelliSense”选项中选择“ 环绕声”命令：
Right-click in the query window and from the popup menu choose the Surround With command:

右键单击查询窗口，然后从弹出菜单中选择“ 环绕声”命令：
Using the combination of keyboard shortcuts Ctrl+K followed by Ctrl+S

结合使用键盘快捷键Ctrl + K和Ctrl + S

Any of these actions will result in appearance of the Surround With window with folders containing SQL snippets:

这些操作中的任何一个都会导致“环绕周围”窗口的外观以及包含SQL代码段的文件夹：

Select the folder to see all available surround SQL snippets from that folder:

选择文件夹以查看该文件夹中所有可用的环绕SQL片段：

Double clicking on the desired surround SQL snippet, will insert a snippet around the selected code. Also to insert a snippet in the query window the Tab or Enter keys can be used.

双击所需的环绕SQL片段，将在所选代码周围插入一个片段。也可以在查询窗口中插入代码段，也可以使用Tab键或Enter键。

Below is the resulting text showing that the BEGIN and END keywords have been added above and below the selected text, respectively:

以下是显示的结果文本，显示分别在所选文本的上方和下方添加了BEGIN和END关键字：

创建自定义片段 (Create custom snippets)

Custom snippets are created as text files in your favorite editor e.g. Notepad

自定义片段在您喜欢的编辑器（例如记事本）中创建为文本文件

To create a custom SQL snippet some rules must be followed:

要创建自定义SQL代码段，必须遵循一些规则：

First, every SQL snippet must start with an XML declaration tag and with specifies the version and encoding:

首先，每个SQL代码段都必须以XML声明标签开头，并指定版本和编码：

<?xml version=”1.0″ encoding=”utf-8″ ?>

<？xml 版本=“ 1.0” 编码=“ utf-8” ？>

The SQL snippet must have the CodeSnippets tag that encapsulates all the snippet definition and contains the namespaces for snippets

SQL代码段必须具有CodeSnippets标记，该标记封装所有代码段定义并包含代码段的名称空间

The CodeSnippet tag contains the Format attribute and two sub-tags (Header and Snippet)

CodeSnippet标记包含Format属性和两个子标记（Header和Snippet）

The Header, sub-tag describes the characteristics of the code snippet, by entering text in the Title, Description, Shortcut, Author and SnippetType elements, which will be displayed in the Code Snippets Manager or when inserting the code in the query window:

Header子标记通过在Title ， Description ， Shortcut ， Author和SnippetType元素中输入文本来描述代码片段的特征，这些元素将显示在代码片段管理器 r或在查询窗口中插入代码时显示：

Title displays the name of the snippet in the Code Snippets Manager windows and in the Insert Snippet context menu:

标题在“ 代码段管理器”窗口和“ 插入 代码段”上下文菜单中显示代码段的名称：
Description describes the purpose of the snippet, it is also displayed in the Code Snippets Manager window, and also shows as a tooltip when inserting a SQL snippet in the query window:

说明描述了代码段的用途，它也显示在“ 代码段管理器”窗口中，并且在查询窗口中插入SQL代码段时也显示为工具提示：
SnippetType defines which category the snippet belongs to e.g. Expression, SurroundsWith or Refactoring.

SnippetType定义代码段所属的类别，例如Expression，SurroundsWith或Refactoring。

More details about a snippet types can be found on the SnippetType Element (IntelliSense Code Snippets) page.

有关代码片段类型的更多详细信息，可以在SnippetType元素（IntelliSense代码片段）页面上找到。

<Header>
<Title>Create Table</Title>
<Shortcut></Shortcut>
<Description>Creates a table with one column.</Description>
<Author>Marko Zivkovic</Author>
<SnippetTypes>
<SnippetType>Expansion</SnippetType>
</SnippetTypes>
</Header>

<标题>
<标题>创建表</标题>
<快捷方式> </快捷方式>
<Description>使用一个列创建一个表。</ Description>
<Author> Marko Zivkovic </ Author>
<SnippetTypes>
<SnippetType>扩展</ SnippetType>
</ SnippetTypes>
</ Header>

The Snippet sub-tag contains the code of the SQL snippet and is composed of two sections: Declaration and Code.

Snippet子标记包含SQL代码段的代码，并由两部分组成：声明和代码。

The Declaration section allows declaration of variables (literals) which can be modified after inserting the snippet in the query window.

声明部分允许声明变量（文字），可以在将片段插入查询窗口后进行修改。

The example below shows how a SQL snippet looks with the default value for the name of the table (e.g. Table_Name) and how will look when user enters values for the name (e.g. MyNewTable):

下面的示例显示了如何使用表名的默认值（例如Table_Name）显示SQL代码片段以及用户输入名称的值（例如MyNewTable）时的外观：

Each variable contains a Literal element which has the following sub-elements:

每个变量都包含一个Literal元素，该元素具有以下子元素：

ID is used to uniquely identify the literal.

ID用于唯一标识文字。

In the case of a duplicate/repeated ID, the repeated value from a SQL snippet will not be highlighted, when the SQL snippet is invoked in the query window.

如果ID为重复/重复，则在查询窗口中调用该SQL代码段时，该SQL代码段的重复值将不会突出显示。

ToolTip is used to display the tool tip when the mouse cursor hovers over a highlighted variable.

当鼠标光标悬停在突出显示的变量上时， ToolTip用于显示工具提示。

Default is used as the default value for the variable that will show up in the snippet when inserted them in the query window

默认被用作该变量的默认值时，在查询窗口中插入他们将在片段显示

The Code section is used to define the code in the CDATA section that will be inserted from the query window. The Language attribute must be specified, which defines the processing language of the code snippet. To use in SSMS, in the Language attribute should be “SQL”:

代码部分用于在CDATA部分中定义将从查询窗口插入的代码。必须指定Language属性，该属性定义代码段的处理语言。要在SSMS中使用，在Language属性中应为“ SQL ”：

<代码语言=“ SQL”>
<！[CDATA [创建表$ SchemaName $。$ TableName $
（
$ Column $ $ DataType $
）; $ end $]]>
</ Code>

The variables $SchemaName$, $TableName$ referring to the name of the IDs that are defined under the Declaration section.

变量$ SchemaName $， $ TableName $引用在“ 声明”部分中定义的ID的名称。

$end$ is used for the location of the cursor after the code snippet is inserted.

$ end $用于插入代码段后的光标位置。

Here is an example of a SQL snippet:

这是一个SQL代码段的示例：

<?xml version=”1.0″ encoding=”utf-8″ ?> <CodeSnippets xmlns=”http://schemas.microsoft.com/VisualStudio/2005/CodeSnippet”>
<CodeSnippet Format=”1.0.0″>
<Header>
&emsp<Title>Create Table</Title>
<Shortcut></Shortcut>
<Description>Creates a table with one column.</Description>
<Author>Marko Zivkovic</Author>
<SnippetTypes>
<SnippetType>Expansion</SnippetType>
</SnippetTypes>
</Header>
<Snippet>
<Declarations>
<Literal>
<ID>SchemaName</ID>
<ToolTip>Name of the schema</ToolTip>
<Default>Customer</Default>
</Literal>
<Literal>
<ID>TableName</ID>
<ToolTip>Name of the table</ToolTip>
<Default>Table_Name</Default>
</Literal>
<Literal>
<ID>Column</ID>
<ToolTip>Name of the column</ToolTip>
<Default>Column_Name</Default>
</Literal>
<Literal>
<ID>DataType</ID>
<ToolTip>Data type of the column</ToolTip>
<Default>int NOT NULL</Default>
</Literal>
</Declarations>
<Code Language=”SQL”>
<![CDATA[CREATE TABLE $SchemaName$.$TableName$
(
$Column$ $DataType$
);$end$]]>
</Code>
</Snippet>
</CodeSnippet>
</CodeSnippets>

<？xml版本=“ 1.0”编码=“ utf-8”？> <CodeSnippets xmlns =” http://schemas.microsoft.com/VisualStudio/2005/CodeSnippet”>
<CodeSnippet Format =“ 1.0.0”>
<标题>
＆emsp <Title>创建表</ Title>
<快捷方式> </快捷方式>
<Description>使用一个列创建一个表。</ Description>
<Author> Marko Zivkovic </ Author>
<SnippetTypes>
<SnippetType>扩展</ SnippetType>
</ SnippetTypes>
</ Header>
<片段>
<声明>
<文学>
<ID> SchemaName </ ID>
<ToolTip>模式名称</ ToolTip>
<默认>客户</默认>
</ Literal>
<文学>
<ID> TableName </ ID>
<ToolTip>表名</ ToolTip>
<默认>表名称</默认>
</ Literal>
<文学>
<ID>列</ ID>
<ToolTip>列名</ ToolTip>
<默认>列名称</默认>
</ Literal>
<文学>
<ID>数据类型</ ID>
<ToolTip>列的数据类型</ ToolTip>
<Default> int NOT NULL </ Default>
</ Literal>
</声明>
<代码语言=“ SQL”>
<！[CDATA [创建表$ SchemaName $。$ TableName $
（
$ Column $ $ DataType $
）; $ end $]]>
</ Code>
</ Snippet>
</ CodeSnippet>
</ CodeSnippets>

在SSMS中注册SQL代码段 (Registering a SQL snippet in SSMS)

After creating the SQL snippet file, the SQL snippet file must be registered via the Code Snippets Manager in order to use it in SSMS. With the Code Snippets Manager, a folder can be added that contains multiple snippets, or by using the Import button from the Code Snippets Manager window to import one or more SQL snippets in the My Code Snippet folder.

创建SQL代码段文件后，必须通过代码段管理器注册SQL代码段文件，以便在SSMS中使用它。使用“ 代码片段管理器” ，可以添加一个包含多个片段的文件夹，或者使用“ 代码片段管理器”窗口中的“ 导入”按钮在“ 我的代码片段”文件夹中导入一个或多个SQL片段。

添加代码段文件夹 (Adding a snippet folder)

To add a folder that contains a SQL snippet, first start SSMS and from the main menu, select the Tools menu, and click the Code Snippets Manager option:

要添加包含SQL片段的文件夹，请首先启动SSMS，然后从主菜单中选择“ 工具”菜单，然后单击“ 代码片段管理器”选项：

This will open the Code Snippets Manager window:

这将打开“ 代码片段管理器”窗口：

Clicking the Add button, will open the Code Snippets Directory window, locate the folder that contains a SQL snippets and click the Select Folder button:

单击添加按钮，将打开“ 代码片段目录”窗口，找到包含SQL 代码片段的文件夹，然后单击“ 选择文件夹”按钮：

After clicking the Select Folder button, the folder MyNewSnippets will appear in the Code Snippets Manager list with all SQL snippets in it:

单击“ 选择文件夹”按钮后，文件夹MyNewSnippets将显示在“ 代码片段管理器”列表中，其中包含所有SQL片段：

导入SQL代码段 (Importing a SQL snippet)

To import individual SQL snippets, open SSMS, from the Tools menu, click the Code Snippets Manager option and from the Code Snippets Manager window choose the Import button:

要导入单个SQL代码段，请打开SSMS，从“ 工具”菜单中，单击“ 代码代码段管理器”选项，然后从“ 代码代码段管理器”窗口中选择“ 导入”按钮：

This will open the Code Snippets Directory window, locate the folder that contains snippets, select one or more the. snippet files, and click the Open button:

这将打开“ 代码片段目录”窗口，找到包含片段的文件夹，然后选择一个或多个。代码段文件，然后单击“ 打开”按钮：

This will open the Import Code Snippet window in which can be chosen location for the snippet that imported. By default, the My Code Snippets folder is checked, but you can choose any user added folder from the list to import a snippet:

这将打开“ 导入代码片段”窗口，可以在其中为导入的片段选择位置。默认情况下，“ 我的代码片段”文件夹处于选中状态，但是您可以从列表中选择任何用户添加的文件夹来导入片段：

Click the Finish button, will import a SQL snippet:

单击完成按钮，将导入一个SQL代码段：

By default the My Code Snippets folder is located under this path: C:\Users\<UserName>\Documents\SQL Server Management Studio\Code Snippets\SQL\

默认情况下，“ 我的代码片段”文件夹位于以下路径下：C：\ Users \ <UserName> \ Documents \ SQL Server Management Studio \ Code Snippets \ SQL \

Importing custom SQL snippets in the query window is the same as importing defaults SQL snippets.

在查询窗口中导入自定义SQL代码段与导入默认SQL代码段相同。

创建一个环绕SQL代码段 (Creating a Surround SQL snippet)

The principle of creating a Surround SQL snippet is the same as creating a custom snippet but with minor changes.

创建Surround SQL代码段的原理与创建自定义代码段的原理相同，只是稍作更改。

In the Header section, the SnippetType XML tag is required, which must have a value “SurroundsWith”:

在“ 标题”部分中，需要SnippetType XML标签，该标签的值必须为“ SurroundsWith ”：

<SnippetType> SurroundsWith</SnippetType>

<SnippetType> SurroundsWith </ SnippetType>

SurroundsWith allows the code snippet to be placed around a selected code in the query window.

SurroundsWith允许将代码段放置在查询窗口中的所选代码周围。

In the Header section under the SnippetType place the SurroundsWith value and in the Code section should be added a $selected$ mark like in the example for the BEGIN END block below:

在SnippetType下的Header部分中，将SurroundsWith值和Code部分中的添加一个$ selected $标记，如下面的BEGIN END块的示例所示：

$selected$ is a place holder for the code selected in the query window that will be inserted into the snippet when invoked.

$ selected $是在查询窗口中选择的代码的占位符，在调用时将插入到代码段中。

The steps for registering a surround SQL snippet in the SSMS is the same as for the custom SQL snippet.

在SSMS中注册环绕SQL片段的步骤与自定义SQL片段的步骤相同。

有用的链接 ( Useful links )

翻译自: https://www.sqlshack.com/sql-snippets-in-sql-server-management-studio/