sql代码格式化
This article will provide a review of SQL code formatting using the SQL formatter options in SSMS
本文将对使用SSMS中SQL格式器选项SQL代码格式进行回顾。
Nobody likes to read a large amount of text, even when it’s just a plain one. When reading SQL script where there is a large amount of non-formatted SQL code, the problem becomes even bigger.
没有人喜欢阅读大量文本，即使只是普通文本也是如此。 在读取包含大量未格式化SQL代码SQL脚本时，问题会变得更大。
SQL code can have different SQL formatting styles, which could make a job either easier or more difficult. It can make code difficult to unscramble and understand. A clean SQL can be read faster than an inconsistently written SQL; SQL reviewing and troubleshooting are more efficient; joint development efforts are more effective; handing off projects from one team to another is easier.
SQL代码可以具有不同SQL格式样式，这会使工作变得更容易或更困难。 它会使代码难以理解和理解。 与编写不一致SQL相比，干净SQL的读取速度更快。 SQL检查和故障排除效率更高； 共同发展的努力更加有效； 将项目从一个团队移交给另一个团队更容易。
How to make code readable so that it is enough just to skim through the code and get the general idea.
如何使代码具有可读性，以便仅浏览代码并获得一般想法就足够了。
It’s the SQL formatter that makes the difference.
是SQL格式化程序才有所作为。
All SQL code can be formatted manually, which is very time consuming but 3rd party SQL formatter tools do exist and can be used to help formatting quickly and efficiently.
所有SQL代码可以手动进行格式化，这是非常费时，但第三方 SQL格式化工具确实存在，可以用来帮助快速高效地格式化。
This article will guide you through some SQL formatter options via SSMS and using 3rd party SQL formatting tool such is ApexSQL Refactor to format code.
本文将指导您完成一些SQL格式化选项通过SSMS和使用第三方 SQL格式化工具，是ApexSQL重构格式化代码。
So, let’s first start with SQL formatter options to format code and then ApexSQL Refactor add-in to do the same thing and more.
因此，让我们首先开始使用SQL格式化程序选项来格式化代码，然后再使用ApexSQL Refactor加载项执行相同的操作，甚至更多。
Indenting
缩进
In SQL Server, three are three options for indenting of SQL code.
在SQL Server中，三个是用于缩进SQL代码的三个选项。
To select desired indenting options, go to the SSMS Tools menu and, from the context menu, choose the Options command:
要选择所需的缩进选项，请转到SSMS 工具菜单，然后从上下文菜单中选择选项命令：
In the Options window, click the Text Editor tab and under the All Languages sub-tab, select the Tabs tab:
在“选项”窗口中，单击“ 文本编辑器”选项卡，然后在“ 所有语言”子选项卡下，选择“ 选项卡” 选项卡：
- None 没有
When this option is selected, in a query editor, when the enter key from the keyboard is pressed, the cursor goes to the beginning of the next line:
选择此选项后，在查询编辑器中，当按下键盘上的Enter键时，光标将移至下一行的开头：
- Block 块
When this option is selected, in a query editor, when press the enter key, the cursor aligns the next line with the previous line:
选中此选项后，在查询编辑器中，按Enter键时，光标会将下一行与上一行对齐：
- Smart 聪明
By default, this option is used. It determines the appropriate indenting style to use.
默认情况下，使用此选项。 它确定要使用的适当缩进样式。
In the Tabs tab, it can be specified how many spaces compose a single indentation or tab, and whether the Editor uses tabs or space characters when indenting.
在“ 制表符” 选项卡中，可以指定组成一个缩进或制表符的空格数量，以及在缩进时编辑器是使用制表符还是空格字符。
If tab characters for indent and tab operations is needed, select the Keep tabs radio button:
如果需要缩进和制表符操作的制表符 ，请选择“ 保留制表符”单选按钮：
But if want to use space characters for indent and tab operations, choose the Insert spaces radio button:
但是，如果要使用空格字符进行缩进和制表符操作，请选择“ 插入空格”单选按钮：
Under the Tab size and Indent size boxes, enter the number of space characters, each tab or indent represents:
在“ 制表符大小”和“ 缩进大小”框中，输入空格字符的数量，每个制表符或缩进表示：
To indent SQL code, in a query editor, select the code that wants to indent and press the Tab key from the keyboard or, from the SQL Editor toolbar, press the Indent button:
要缩进SQL代码，请在查询编辑器中选择要缩进的代码，然后从键盘上按Tab键，或者在SQL编辑器工具栏中，按缩进按钮：
For unindenting code press the Shift+Tab keys or use the Unindent button from the SQL Editor toolbar:
对于缩进代码，请按Shift + Tab键或使用“ SQL编辑器”工具栏中的“ 缩进”按钮：
The same options for Indent and unindent code are available under the Advanced sub-menu of the Edit menu:
在“ 编辑”菜单的“ 高级”子菜单下，可以使用缩进和缩进代码的相同选项：
To convert spaces to tabs in SQL code and vice versa in a query editor, select the spaces that want to convert in tabs, go to the Edit menu and under the Advanced sub-menu, choose the Tabify Selected Lines command for converting white space to tabs and choose the Untabify Selected Lines command to convert tabs to spaces:
要将空格转换为SQL代码（在SQL代码中为制表符，反之亦然），请选择要在制表符中进行转换的空格，进入“ 编辑”菜单，然后在“ 高级”子菜单下， 选择 使用Tabify Selected Lines命令将空格转换为制表符，然后选择Untabify Selected Lines命令将制表符转换为空格：
To see if spaces in a code are converted to tabs and vice versa, go to the Edit main and, under Advanced sub-menu, choose the View Whit Space command:
要查看代码中的空格是否转换为制表符，反之亦然，请转到“ 编辑”主菜单，然后在“ 高级”子菜单下，选择“ 查看Whit Space”命令：
For example, if the Tabify Selected Lines command in the code to convert spaces in tabs is used, the following code:
例如，如果使用代码中的“ Tabify Selected Lines”命令来转换制表符中的空格， 以下代码：
will look like this:
看起来像这样：
And when the Untabify Selected Lines command is used to convert tabs into spaces, the SQL code will look like this:
当使用“ 取消选中的行的选项”命令将制表符转换为空格时，SQL代码将如下所示：
Whenever the data migration from any old systems to new/upgraded systems, using different formats like text files, csv, excel or some other format to insert data into SQL Server, the additional white spaces can be added, like in the example below:
每当将数据从任何旧系统迁移到新/升级系统时，使用文本文件，csv，excel或其他格式的不同格式将数据插入SQL Server时，都可以添加额外的空格，如以下示例所示：
Luckily, these white spaces can be easily removed using the Delete Horizontal White Space option. In a SQL editor, select the code and under the Advanced sub-menu of the Editor menu, choose the Delete Horizontal White Space command:
幸运的是，可以使用“ 删除水平 空白”选项轻松删除这些空白。 在SQL编辑器中，选择代码，然后在“编辑器”菜单的“高级”子菜单下，选择“ 删除水平 空白”命令：
The Delete Horizontal White Space removes all spaces for the selected SQL code:
删除水平 空白会删除所选SQL代码的所有空格：
Another way to get rid of unnecessary white spaces is to hold the Alt key and left mouse click, select the block of white spaces:
摆脱不必要的空白的另一种方法是按住Alt键并单击鼠标左键 ，选择空白块：
After selecting press the Delete key to remove those spaces:
选择后，按Delete键删除这些空格：
Alternately, with this type of selection, a text can be added multiple times at once. To clarify, to add a table alias to columns in this case the a alias:
或者，使用这种选择类型，可以一次添加多次文本。 澄清一下，在这种情况下，将表别名添加到列中是别名：
Press the Alt key on the keyboard and left click on the mouse, click and drag to select the desired block before columns, as you may notice a very light vertical blue line will appear, after that just type the latter a with the dot (.) and will be placed in front of the all columns:
按下键盘上的Alt键，然后在鼠标上单击鼠标左键，单击并拖动以在列之前选择所需的块，因为您可能会注意到会出现一条非常浅的垂直蓝线，之后，请用点（。）键入后者。 ），并将其放置在所有列的前面：
Converting SQL code to Upper or Lower case
将SQL代码转换为大写或小写
To convert code to be in upper case in a query editor, select a code that wants to convert and under, the Advanced sub-menu, choose the Make Uppercase command or use the Ctrl+Shift+U shortcut:
要在查询编辑器中将代码转换为大写，请选择要转换的代码，然后在“ 高级”子菜单下，选择“使用大写”命令或使用Ctrl + Shift + U快捷键：
The selected code will be formatted like this:
所选代码的格式如下：
To covert code to be in lower case, select desired code and use the Make Lowercase command or Ctrl+Shift+L shortcut:
要将代码隐藏为小写，请选择所需的代码，然后使用“ 使小写”命令或Ctrl + Shift + L快捷键：
Wrapping SQL code
包装SQL代码
When T-SQL scripts were created with lengthy T-SQL lines, to review code in such T-SQL scripts either use the horizontal scroll bar or create line breaks at various points to make the line readable and rid of the horizontal scrollbar. Scrolling or having to break the lines of code reviewing of code can be time consuming.
使用长T-SQL行创建T-SQL脚本时，要查看此类T-SQL脚本中的代码，请使用水平滚动条或在各个点创建换行符以使行可读并摆脱水平滚动条。 滚动或不得不中断代码行是很耗时的。
In SQL Server Management Studio (SSMS) there is an option for word wrapping.
在SQL Server Management Studio（SSMS）中，有一个自动换行选项。
From the Tools menu, select the Options command:
从“ 工具”菜单中，选择“ 选项”命令：
In the Options window, expand the Text Editor tab and under the General tab of All Languages, the Word wrap option is located:
在“ 选项”窗口中，展开“ 文本编辑器”选项卡，然后在“ 所有语言”的“ 常规”选项卡下，找到 “自动换行”选项：
By default, this option is unchecked. When the Word wrap option is checked the Show visual glyphs for word wrap option becomes available:
默认情况下，未选中此选项。 选中自动换行选项时， 显示自动换行的可视字形选项变为可用：
The Show visual glyphs for word wrap option placed marks on every line where the word wrapping is applied.
显示自动换行的显示可视字形选项在应用自动换行的每一行上都放置了标记。
In the example below a SQL code is shown without Word wrap and Show visual glyphs for word wrap options and with these options checked.
在下面的示例中，显示了一个SQL代码，其中没有自动换行和显示自动换行选项的可视字形，并且选中了这些选项。
Code without Word wrap and Show visual glyphs for word wrap options checked:
没有自动换行的代码，并选中了自动换行选项的显示可视字形 ：
Code with Word wrap and Show visual glyphs for word wrap options checked:
带有自动换行的代码，并选中了自动换行选项的显示可视字形 ：
As it can be seen, the horizontal scrollbar is gone and places, where the word wrap have been applied, are marked.
可以看出，水平滚动条消失了，并标记了应用自动换行的位置。
3rd party SQL formatter tool
第三方 SQL格式化工具
This part of article will explain in base what can be archived with ApexSQL Refactor free SQL formatter tool.
本文的这一部分将在基础上说明可以使用ApexSQL Refactor免费的SQL格式化工具存储的内容。
First that can be noticed when install ApexSQL Refactor is that immediately can be used to format SQL code by using one of four options in SQL formatting profiles:
首先，在安装ApexSQL Refactor时要注意的是，通过使用SQL格式化配置文件中的四个选项之一，可以立即用于格式化SQL代码：
- ApexSQL – contains what ApexSQL determined that is a good SQL formatter standard ApexSQL –包含ApexSQL确定的是良好SQL格式化程序标准的内容
- Compact – all options for spacing are unchecked, indentions options set to 0 (zero) space, removed empty lines for a query where the SQL code looks dense 紧凑 -间距的所有选项均未选中，缩进选项的空间设置为0（零），在SQL代码看起来密集的查询中删除了空行
- Extended – add spaces, empty lines before/after every statement. In base, this profile is opposite of the Compact profile 扩展 –在每个语句之前/之后添加空格和空行。 在基础上，此轮廓与紧凑轮廓相反
- MSDN SQL BOL – emulates the style that is used in the MSDN SQL BOL –模拟在MSDN resource site MSDN资源站点中使用的样式
If those doesn’t meet your needs a new SQL formatting profile can be created simply by clicking the New button on the Options window and fill in information about the name of a new SQL formatter profile and press the OK button:
如果这些都不满足您的需求，只需单击“ 选项”窗口上的“ 新建”按钮并填写有关新SQL格式化程序配置文件名称的信息，然后按“ 确定”按钮，即可创建新SQL格式配置文件：
Under the Formatting tab can be set indentation and word wrap text and avoiding horizontal scrollbar in a query editor by checking the Wrap lines longer than checkbox and set how many characters a line will be long:
在“ 格式”选项卡下，可以设置缩进和自动换行文本，并在查询编辑器中避免水平滚动条，方法是选中“ 换行的时间长于复选框”并设置行长的字符数：
In the Capitalization tab can be set a various capitalization rule for the SQL keywords, Data types, Identifiers, System functions, Variables separately:
在大写选项卡中，可以分别为SQL关键字，数据类型，标识符，系统函数，变量设置各种大小写规则：
Even better all changes that are made when choosing some of the options are immediately visible in the preview section of the Options window:
更好的是，在选择某些选项时所做的所有更改都可以在“ 选项”窗口的预览部分中立即看到：
Another advance of using ApexSQL Refactor is his ability to share custom created profiles and make it accessible to others, by clicking the Export button:
使用ApexSQL Refactor的另一个进步是，他可以共享自定义创建的配置文件，并通过单击“ 导出”按钮使其他人可以访问它：
Co-workers can import shared SQL formatting profile and multiple apply it on theirs SQL objects (stored procedures, functions, views) or SQL scripts by using Format SQL objects feature for SQL objects or Format SQL scripts feature for SQL scripts at once.
同事可以一次使用SQL对象的Format SQL对象功能或SQL脚本的Format SQL脚本功能，导入共享SQL格式配置文件并将其应用于他们SQL对象（存储过程，函数，视图）或SQL脚本。
More about ApexSQL Refactor SQL formatter features and options can be found on the Top things you need in a SQL formatter tool page.
有关ApexSQL重构SQL格式化程序功能和选项的更多信息，请参见SQL格式化程序工具页面上所需的首要内容 。
翻译自: https://www.sqlshack.com/manage-sql-code-formatting-using-sql-formatter-options/
sql代码格式化