• SQL Prompt
• SQL Formatting and Styles

Bulk Formatting of the SQL Server SQL Files

For practically every purpose, it is better to format SQL Server SQL Files using SQL Prompt, within your favorite query editor. However, there are several development tasks that require you to do the formatting as part of a process, or in bulk. For this, the SQL Toolbelt and SQL Toolbelt Essentials bundles provide a DOS command-line formatter that uses the same codebase as SQL Prompt. This means you can use SQL Prompt to refine the way you or your organization formats SQL code, and then use those same settings with the bulk formatter and run it from the command line to format a collection of SQL files.

Why a bulk formatter?

If all SQL code was lovingly hand-crafted by misty-eyed artisan developers, there wouldn’t be a need for code formatting. Unfortunately, the real life of a database developer involves compromises and occasionally, the use of automatically generated code.

If you use a GUI development tool, such as an Entity-Relationship Modelling tool, to generate build scripts or object-source directories from the live database, or from some other automated process, then you are increasingly likely to be faced with a problem. Nowadays, you are required to meet the organizations’ standards for code, to make code reviews easier and facilitate teamwork. If you, like me, have been faced with a large directory of code that needs to be tidied up and made presentable, to meet ‘quality objectives’, then a bulk code-formatter will seem attractive.

Processing a collection of synch scripts in SSMS, even with SQL Prompt, soon makes watching paint dry seem unbearably exciting by contrast. Imagine having to format each of the Object-level directory of build scripts generated by SQL Compare, one by one in SSMS, before you commit it to source control. It could contain maybe hundreds of individual object-level build scripts. Yeah, for this sort of task, you need SqlPrompt.Format.CommandLine.exe.

There are other occasional uses for bulk formatting, such as when preparing SQL code for other RDBMSs. Obvious examples are when you’re obliged to port to case sensitive RSBMSs when your code is mixed case or when putting semicolons in for most relational database systems when all your code is written for SQL Server or Sybase.

SQL Prompt command line parameters

The command line formatter is provided in a zip file called FormatCommandLine.zip in the SQL Prompt installation directory (usually C:\Program Files (x86)\Red Gate\SQL Prompt 10). Firstly, you need to create a folder, such as C:\Program Files (x86)\Red Gate\SQL Formatter. Then extract the zip file to this new directory. Then you must add the path of your new directory to the list of paths that the system uses to search for a command (i.e., to the PATH Environment variable.

The following table explains the parameters it accepts. As well as bulk-applying the built-in or custom formatting styles that just make the code readable by adding or removing whitespace, you can specify settings for any of the Prompt formatting actions that can be run automatically with Format SQL (this doesn’t affect the default settings for these actions in Options | Styles).

Long-Form Parameter	Short-form parameter	Meaning, and parameter arguments
–i-agree-to-the-eula		By using this option, you consent to the Redgate EULA found at End User License Agreement when running the command-line formatter. (This argument is required.)
–path	-p	You can use the formatter for just a file or an entire directory. Just provide either the file or directory to format. (This argument is required.) The path must be in double quotes in the command-line if you have a space anywhere in the path.
–create-backups	-b	Create backups of formatted files. The original SQL file will be backed up to a file with a .bak extension, located next to the original file. If there is already a backup file in the directory, the formatter produces an anonymous and inscrutable error. If you don’t specify this parameter, it doesn’t create backups.
–style	-s	The name of a built-in Redgate JSON layout formatting style, or the path to the JSON file for a custom style. If no style is specified, laying out of SQL will be skipped. The path must be in double quotes at the command-line if you have a space anywhere in the path. The name of the built-in Redgate formatting style can be one of: Default Collapsed Commas before Indented Right aligned They are all case-sensitive. “Right aligned” and “Commas before” need to be in double quotes, when used at the command line.
–no-layout	-nl	Do not apply any style layout. (a layout formatting style specified by the –style parameter uses whitespace to alter the layout of code, rather than changing the code itself.
–apply-casing	-ac	Apply global casing options when formatting, but if you don’t specify a style lowercase UPPERCASE lowerCamelCase UpperCamelCase
–prefix-unicode	-n	Prefix N to Unicode literal string variable assignments. (N stands for National Language Character Cet)
–insert-semicolons	-is	Insert semicolons at the end of SQL queries and statements.
–enclose-identifiers	-e	Add/remove square brackets. The possible values are: WithBrackets – add brackets to all identifiers WithoutBrackets – remove unnecessary brackets
–table-alias-style	-ta	Add/remove AS keyword on alias definitions for tables and views. Possible values: AddAs – add AS keyword RemoveAs – remove AS keyword
–column-alias-style	-ca	Apply column alias style. Possible values: AsWithoutString (e.g., column AS alias) AsWithString (e.g., column AS "alias") EqualsWithoutString (e.g., alias = column) EqualsWithString (e.g., alias = "column") NoAsWithoutString (e.g., column alias) NoAsWithString (e.g., column "alias")
–version		Shows the current version number of the command line formatter.
–help	-h, -?	the command line help shows this information listing the available options.

Using the Formatter at the command prompt

To test this out, we’ll create a directory whose path is S:\work\programs\format, place copies of a whole lot of SQL Files in it, and then format all of them in the Default style, while also removing unnecessary square brackets, and specifying table and column alias styles.

C:\Users\phil>SqlPrompt.Format.CommandLine.exe -ta AddAs --style Default -ca AsWithString --path S:\work\programs\format -e WithoutBrackets --i-agree-to-the-eula  -b
Red Gate SQL Prompt 10.11 formatter - Copyright © Red Gate Software Ltd 2006-2022
Formatting 'S:\work\programs\format'
Creating backups of formatted files
Formatting options:
  Applying layout using style 'Default'
  Enclosing identifiers: WithoutBrackets
  Setting table alias style: AddAs
  Setting column alias style: AsWithString
[4%] Formatting AddingDescriptions.sql... Done
[7%] Formatting bcp out.sql... Error: Incorrect syntax near ?.
[11%] Formatting CheckNonSchemaBound.sql... Done
[14%] Formatting DeleteAllDataInDatabase.sql... Done
[18%] Formatting GetColumnsAndProperties.sql... Error: Incorrect syntax near SELECT.
[21%] Formatting GetListOfDatabases.sql... Done
[25%] Formatting GetTablesAndColumns.sql... Done
[29%] Formatting instantTheses.sql... Error: expression is not a recognized table or a view hint.
[32%] Formatting KillExtendedEvents.sql... Done
[36%] Formatting LinkServerStruggle.sql... Error: Incorrect syntax near sp_addlinkedserver.
[39%] Formatting MoveForeignKeyReferences.sql... Done
[43%] Formatting PhilFactorsOtherStuff.sql... Done
[46%] Formatting Placeholders.sql... Done
[50%] Formatting ReadEveryTableViaBCP.sql... Done
[54%] Formatting SaveAndReadAllTablesViaBCP.sql... Error:
[57%] Formatting SaveEveryTable.sql... Done
[61%] Formatting SaveExtendedProperties.sql... Error:
[64%] Formatting SaveExtendedPropertiesa.sql... Done
[68%] Formatting SaveTableViaBCP.sql... Error:
[71%] Formatting TableAttributeScript.sql... Done
[75%] Formatting TableOfDocuments.sql... Done
[79%] Formatting TableSizes.sql... Done
[82%] Formatting TableSmellsJSON.sql... Done
[86%] Formatting TestAssertionStringSplitFunction.sql... Done
[89%] Formatting TestModuleDependencies.sql... Done
[93%] Formatting testTransactionRollback.sql... Error: Incorrect syntax near start.
[96%] Formatting WaitHistogram.sql... Done
[100%] Formatting WhoIsBusy.sql... Done

That went quite well. Occasionally, you’ll see an error. If the formatter finds a syntax error, you will get an error description. However, this a “v1” release of the command line formatter, with a few wrinkles to iron out. Sometimes, for example, it won’t tell you what the error is, such as when you specify -b and there is already a backup of the file it is formatting. Now and again, I also found it flagged an error against a file for no obvious reason, even when the file formatted sweetly in SQL Prompt (I expect these sorts of bugs to be fixed shortly). There are one or two other curiosities too. For example, the parameters are case-sensitive, which is unusual for a Windows app (although BCP and SQLCMD are both infamous for this.)

C:\Users\phil>SqlPrompt.Format.CommandLine.exe --I-agree-to-the-EULA
Option '--i-agree-to-the-eula' is required.

Using the SQL Formatter with PowerShell

You can check to see if the path to SqlPrompt.Format.CommandLine.exe is set properly:

The neatest way of using a utility like this is to provide an alias. If, for some reason, you were reluctant to update the PATH environment variable, you would need to specify the entire path to the command.

Now you can use the formatter conventionally.

It is usually better to ‘splat’ if your command-line tool allows this.

…and you can use it from then on like this …

rgFormat @Splat
PS C:\WINDOWS\system32> rgFormat @Splat
Red Gate SQL Prompt 10.11 formatter - Copyright ¸ Red Gate Software Ltd 2006-2022
Formatting 'S:\work\programs\format'
Creating backups of formatted files
Formatting options:
  Applying layout using style 'Default'
  Enclosing identifiers: WithoutBrackets
  Setting table alias style: AddAs
  Setting column alias style: AsWithString
[4%] Formatting AddingDescriptions.sql... Done
rgFormat :  Error: Incorrect syntax near ?.
At line:1 char:1
+ rgFormat @Splat
+ ~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: ( Error: Incorrect syntax near ?.:String) [], RemoteException
    + FullyQualifiedErrorId : NativeCommandError
 
[7%] Formatting bcp out.sql...[11%] Formatting CheckNonSchemaBound.sql... Done
[14%] Formatting DeleteAllDataInDatabase.sql... Done
 Error: Incorrect syntax near SELECT.
[18%] Formatting GetColumnsAndProperties.sql...[21%] Formatting GetListOfDatabases.sql... Done
[25%] Formatting GetTablesAndColumns.sql... Done
 Error: expression is not a recognized table or a view hint.
[29%] Formatting instantTheses.sql...[32%] Formatting KillExtendedEvents.sql... Done
 Error: Incorrect syntax near sp_addlinkedserver.
[36%] Formatting LinkServerStruggle.sql...[39%] Formatting MoveForeignKeyReferences.sql... Done
[43%] Formatting PhilFactorsOtherStuff.sql... Done
[46%] Formatting Placeholders.sql... Done
[50%] Formatting ReadEveryTableViaBCP.sql... Done
 Error: 
[54%] Formatting SaveAndReadAllTablesViaBCP.sql...[57%] Formatting SaveEveryTable.sql... Done
 Error: 
[61%] Formatting SaveExtendedProperties.sql...[64%] Formatting SaveExtendedPropertiesa.sql... Done
 Error: 
[68%] Formatting SaveTableViaBCP.sql...[71%] Formatting TableAttributeScript.sql... Done
[75%] Formatting TableOfDocuments.sql... Done
[79%] Formatting TableSizes.sql... Done
[82%] Formatting TableSmellsJSON.sql... Done
[86%] Formatting TestAssertionStringSplitFunction.sql... Done
[89%] Formatting TestModuleDependencies.sql... Done
 Error: Incorrect syntax near start.
[93%] Formatting testTransactionRollback.sql...[96%] Formatting WaitHistogram.sql... Done
[100%] Formatting WhoIsBusy.sql... Done
PS C:\WINDOWS\system32>

Some of these errors are legitimate but, again, we are getting errors with no descriptions. The first error, while formatting AddingDescriptions.sql file, is one of the ‘mysterious’ ones: the file works fine when formatted by SQL Prompt, and it works fine in DOS rather than PowerShell.

Executing the SQL formatter from a DOS batch

You may want to be able to run this same command with identical parameters in the command line, especially if you need to run processes in DOS batches or integrate it with other applications that use a CLI interface. We cannot use the ‘Splat’ directly, but we can generate the command-line with all its parameters from the Splat and use that string to execute exactly the same operation in a DOS batch. This PowerShell uses the data that we put in the $Splat array to create a command-line which you can cut and paste into a DOS prompt.

This works exactly as the first example worked at the command-line.

Bulk applying custom styles

When you edit a style, SQL Prompt shows you the path to your style folder where all your custom files are kept. You van view them…

I would be nervous about using the actual style (each style has all sort of ‘uniqueifier’ numbers in the file name). Instead, I’ll copy it across and give it a simpler name; it is fine to put it in the same directory as the SQL Files on which you are trying out the utility.

Now you just supply the path to the style rather than the name of the built-in command:

Which gives the same result as before but with nicer formatting!

Adding formatting to your favorite Text Editor

Although I do most of my work in SSMS, using SQL Prompt, I also use a special-purpose programmer’s editor for the difficult bits. These include long files, tricky code conversions requiring lots of regex strings, simultaneous editing of application code and SQL code and rapid editing of many files. The best programmers’ editors will have a command-line level interface to allow you to use the many code-formatting tools around. We’ll embed SQL formatting in Editpad Pro just to illustrate.

I should mention, though, that it was at this point that I discovered how slow SQL formatting can be with very long SQL files. It isn’t surprising considering the work it is having to do. Still, I was doing other things during those two hours that it took.

Editpad Pro, like most programmers’ editors, has a menu for external tools. We’ll want a menu item for formatting SQL Files. Here is the existing Tools menu. We click on Configure Tools….

This shows the way I set the Definition tab panel:

The command line would be …

if there is a space anywhere in the path to the file, you’d need to use "%FILE%".

In the Files tab panel, I select:

• ‘Save the current file if it has unsaved changes’
• ‘Reload the current file’.

In the Standard I/O tab panel I choose:

• ‘What to send to the tools standard input‘ = ‘nothing’
• ‘How to capture the tool’s standard output’ = ‘into the message panel’.
• ‘How to capture the tool’s standard error’ = ‘Combine with output’.

That’s it. With a certain apprehension I give it a try…

And lo, it works!

Conclusion

I don’t think there is any argument about it. A command-line SQL Formatter is always useful for certain jobs. When you have it closely integrated with SQL Prompt, so that all your style choices and other preferences are consistent between the two, then the chances of getting home on time are dramatically increased.

This is the second SQL Prompt tool to have a command line equivalent. SQL Code analysis (previously known as SQL Code Guard) also has a command line and I’ve already shown, in several articles, the ways that this can be useful. See, for example, SQL Code Analysis from a PowerShell Deployment Script or Running SQL Code Analysis during Flyway Migrations.

One of the most consistent aspects of the DevOps revolution in development is the relaxing of the old industry tendency to try to lock users into using a single IDE for all aspects of a development. DevOps teams like to be able to mix and match tools to process workflows and get more control over application and database delivery. For this, the CLI interface is an important element, because it makes the integration of components such as source control, alerting, bug-handling, build, test, and other workflows much easier.

Tools in this post

SQL Prompt

Write, format, and refactor SQL effortlessly

Find out more

SQL Toolbelt

The industry-standard tools for SQL Server development and deployment.

Find out more