Automate code formatting from the command line
Write, refactor, and beautify your SQL code and dramatically boost your productivity with the features included in SQL Complete. You can automate code formatting in SQL Complete using the command line or in the DevOps process.
The guide describes the following topics:
- How to automate code formatting from the command line
- How to format scripts in the CI process
- Switches used in the command line
- Exit codes used in the command line
How to automate code formatting from the command line
1. Click Start.
2. Type cmd in the Search programs and files text box and press Enter.
Note
To run the Command Line in Windows 10:
From the Desktop Mode
- Move the mouse cursor to the lower left corner until the desktop shortcuts menu appears.
- Right-click to see the shortcut menu and click Run.
From the Charms Bar
- Move your mouse to the upper right corner until the Charms Bar appears.
- Select Apps from the list and type ‘run’ in the search box.
- Click Run from the search results.
Use Windows + R Shortcut Keys
- In desktop mode, press the Windows + R keys to show the Run command line.
- In the window that opens, type cmd and press Enter.
3. Type a path to the sqlcomplete.com file located in the SQL Complete installation folder on your PC, and then press Enter.
cd "C:\Program Files (x86)\Devart\dbForge SQL Complete"
4. Specify the operation you want to perform (code formatting) and the required parameters and arguments:
sqlcomplete.com /formatsql /sourcefile:"D:\NoFormat.sql" /outputfile:"D:\Format.sql"
Tip
To get quick help on the arguments available in the command line, type the following:
sqlcomplete.com /formatsql /?
5. Press Enter to run the process.
How to format scripts in the CI process
In database development, Continuous Integration (CI) automates the building and testing process each time the development team modifies the database project scripts. This helps quickly detect defects and instantly notify developers about the changes made.
The Database CI involves the following stages:
- Development: Changes are made to the database project schema.
- Version Control: Developers commit the changes to the version control system.
- Build: The database is created from .sql scripts.
- Unit Test: The database is tested using unit tests.
- Publish: An artifact with the database changes, such as a NuGet package, is published.
dbForge SQL Complete can be involved in the automated Continuous Integration (CI) process at the development stage. The PowerShell Invoke-DevartFormatScript cmdlet is required to automate the formatting of scripts to update or create a database according to company standards before sending the scripts into a NuGet package. The script invokes the Invoke-DevartFormatScript cmdlet, which initiates the formatting functionality of SQL Complete. You can format the entire folder with multiple .sql files or a single .sql file using predefined or custom formatting profiles.
In this PowerShell script, you need to specify only a path to the .sql file or folder with .sql files you want to format and a formatting profile you want to use. For more information, see Integrate SQL Complete into DevOps.
Switches used in the command line
Select corresponding command line switches to perform the required operations:
| Argument | Usage and Action |
|---|---|
| /argfile | Usage: /argfile:<path>Path to the command line arguments file. The options specified in the command line have higher priority than the options which were given in the file. |
| /activate | Activate the product using the activation key. For more information, see the Activation topic. |
| /connection | Usage: /connection:<connection_string>Specify the connection string. For example, /connection:”Data Source=JordanS\MSSQLSERVER2019;Initial Catalog=AdventureWorks2019; User ID=yourusername;Password=yourpassword” |
| /deactivate | Deactivate the product. For more information, see the Deactivation topic. |
| /encoding | Usage: /encoding:<codepage|encoding name>Specify the encoding that should be applied to open and save files. For example, /encoding:”UTF-8” or /encoding:”65001” |
| /errormode | Usage: /errormode:<ignore|abort>Specify the application behavior when an error is encountered: ignore - to ignore all errors; abort - to cancel execution if any error occurs. |
| /exitcodes | The list of possible exit codes that can be returned by the command-line process. |
| /filextension | Usage: /filextension:*.<sql|prc|txt|...>Specify the extension of the source folder files that will be formatted. The .sql extension is selected by default. |
| /formatsql | Format SQL files with a specified profile. |
| /includesubfolders | Usage: /includesubfoldersInclude the current directory and all of its subdirectories in the search operation. |
| /log | Usage /log:<filepath>Generate a formatting log file in the specified directory. |
| /outputfile | Usage: /outputfile:<path>Specify the path to an output file where the formatting results of the source file will be saved (in this case, the source file is saved without changes). If the parameter is not specified, the formatting results will be saved in the source file. |
| /outputfolder | Usage: /outputfolder:<path>Specify the path to an output folder where the source folder files with the formatting results will be saved. The files from the source folder will not be changed. If the parameter is not specified, the formatting results will be saved in the source folder files. |
| /password | Usage: /password:<yourpassword>Specify the server password. This overrides the password specified in the connection string. |
| /profile | Usage: /profile:<path>|<profile name>Specify the path to the file that contains formatting options settings or a predefined profile name. If the parameter is not specified, default formatting options settings will be applied. For example, a profile shared through network with dev team: /profile:”\pub\devteam\corporate_style_profile.xml” or a profile stored on a local machine: /profile:”D:\formatprofile\myprofile.xml” |
| /quiet (/q) | Hide all messages in the console mode. |
| /sourcefile | Usage: /sourcefile:<path>Specify the source SQL file to be formatted. |
| /soucefolder | Usage: /sourcefolder:<path>Specify the path to the folder where the files for formatting are located. |
| /treatwarningaserror | Usage: /treatWarningAsError:[Yes|No]Yes: Treat all warnings as errors; further behavior is determined by the errormode option. No: Do not treat all warnings errors and execute with warning messages. |
Exit codes used in the command line
| Exit code | Description | Additional information |
|---|---|---|
| 0 | Success | The operation has been completed without errors. |
| 1 | Unhandled exception | Unhandled exception. See the log for more details. |
| 10 | Command line usage error | The command line was used incorrectly. For example, an incorrect flag, or incorrect syntax may have been used. |
| 11 | Illegal argument duplication | Some arguments may not appear more than once in a command line. For example, /arg2 depends on /arg1 but you have specified /arg2 without specifying /arg1, or /arg2 cannot be used with /arg1, but you have used them both. |
| 20 | Trial expired | Software trial period has expired, or the product has not been activated. |
| 40 | Server connection fail | Server connection failed. |
| 104 | High-level parser error | High-level errors were encountered while parsing a scripts folder. |
| 105 | Resource unavailable | The file is missing. |
Additionally, you can watch this video to learn how to format SQL script files from the command line and how to automate the process using PowerShell.
Also, if you want to know how to involve dbForge SQL Complete in the Database DevOps process, feel free to watch this video.
Want to Find out More?
Overview
All features
Request a demo
