Bulk operations via the command line
Published 12 April 2021
Formatting
SQL Prompt ships with a formatter that can be run from the command line to format a collection of SQL files.
Where to find the command line formatter
The command line formatter can be found inside a zip file called FormatCommandLine.zip in the SQL Prompt installation directory (usually C:\Program Files (x86)\Red Gate\SQL Prompt 10).
To run the command line formatter, extract the zip file to a directory and run SqlPrompt.Format.CommandLine.exe with the desired arguments.
Adding the command line formatter to your PATH variable
To make the command line formatter accessible within a command prompt (e.g. Windows Terminal) without specifying its full path, you can add the formatter to your PATH environment variable:
- Search path in the start menu, and click on Edit environment variables for your account:
- Click on Environment Variables, then double click on the entry titled Path. Then press New and add the path of the folder you have extracted the command line formatter to:
- You should then be able to use SqlPrompt.Format.CommandLine.exe directly within your command prompt window.
Running the command line formatter
To run the SQL Prompt formatter from the command line, run SqlPrompt.Format.CommandLine.exe with the relevant arguments:
| Option | Alias | Description |
|---|---|---|
| --i-agree-to-the-eula | Confirms that you agree to the End User Licence Agreement when running the command line formatter. This argument is required. | |
| --path | -p | The file or directory to format. This argument is required. |
| --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. |
| --style | -s | The name of a Redgate formatting style, or the file path of a formatting style file (.json). If no style is specified, laying out of SQL will be skipped. The name of the Redgate formatting style can be one of:
|
| --no-layout | -nl | Do not apply style layout |
| --apply-casing | -ac | Apply casing options when formatting. The options are defined in the Global → Casing section of a formatting style. |
| --prefix-unicode | -n | Prefix N to unicode literal string variable assignments. |
| --insert-semicolons | -is | Insert semicolons. |
| --enclose-identifiers | -e | Add/remove square brackets. Possible values:
|
| --table-alias-style | -ta | Add/remove AS keyword on alias definitions for tables and views. Possible values:
|
| --column-alias-style | -ca | Apply column alias style. Possible values:
|
| --version | Shows the current version number of the command line formatter. | |
| --help | -h, -? | Shows the command line help that lists the available options. |
Omitting any argument will cause that formatting action to be skipped.
When running, the command line formatter will summarize the options that it is using to apply the format:
As it processes each SQL file to be formatter, the command line formatter will show progress and any errors encountered:
