The appearance of SQL code can be customized separately for each database kind that is supported by DatabaseSpy. In the SQL Formatting page of the Options dialog box, you can furthermore disable this formatting for the SQL Editor and/or the Database Structure Change Script window. Both check boxes are activated by default.
Any changes you make to the default formatting are recorded in a configuration file, at the following path: ..\Documents\Altova\DatabaseSpy2020\SQLFormattingConfigurations.qfmt. Please note that the configuration file is not automatically transferred during upgrades if the installation folder changes. After upgrading to a new major version, you have to copy the configuration file to the new installation folder (..\Documents\Altova\DatabaseSpy<Version>) yourself.
|Note:||As of version 2009, a new version number is used for configuration files. Therefore, configurations that have been saved in DatabaseSpy 2009 cannot be opened in earlier releases.|
The formatting is applied when you generate SQL (see Generating SQL Statements). In addition, you can always click the Apply SQL Formatting to the active SQL Editor button.
You can format the way SQL code is displayed separately for a variety of database kinds:
•MS SQL Server 2005
•MS SQL Server 2008 and greater
•MS Access 2003 and greater
•MySQL 5 and greater
•Oracle 9 and 10
•Oracle 11 and greater
•IBM DB2 8 and greater
•IBM iSeries 5.4 and greater
•IBM Informix 11.1 and greater
•Sybase 12 and greater
•SQLite 3 and greater
•PostgreSQL 8.3 and greater
•Any Other Database (ANSI 2003 syntax)
The SQL Formatting Configurations dialog box displays a tree structure which allows you to define either the general formatting options on root level or specify formatting options more specifically for the individual statement types.
In the Preview group box, the options are immediately applied to a sample statement. Selecting a specific statement type in the tree structure displays the relevant part of the sample statement in the Preview group box.
Options on root level
If you select the root level of a database type, you can either deactivate the formatting options for this individual database type (without deactivating the SQL formatting feature as a whole), or define which case should be used for identifiers, functions, and keywords, when to add spacing and blank lines, and how far text should be indented.
To deactivate SQL formatting for a certain database kind:
1.Select the menu option Tools | Options or press Ctrl+Alt+O to open the Options dialog box and click the SQL Formatting Configurations... button on the SQL Formatting page.
2.In the SQL Formatting Configurations dialog box, select the respective database kind from the drop-down list in the Configurations group box.
3.In the Options section, select the Ignore All check box.
Options for the individual statement types
When you select one of the statement types in the tree structure, the corresponding formatting options are displayed in the window on the right side of the dialog box. Different options are available for the individual statement types.
The header of the options list provides an icon on the right edge that displays the properties inherited from the root level. If you click the Show inherited properties icon, the options from the root level are displayed and you can change the settings for the selected statement type. Note that these changes apply only to the selected statement type; for all other parts of the statements, the settings defined on the root level are still valid! Options that differ from those defined on root level appear in bold type. These options remain also visible if you click the Hide inherited properties button.
You can customize the display of the following statement types (click a statement type to view the formatting options applicable to it):
|Note:||Some statement types listed above are not available for configuration when they are not applicable to the selected database kind.|
You can remove all additional whitespace and line feeds that have been added by the Format SQL command or because of the default setting for formatting SQL in the SQL Formatting options. The menu option SQL Refactoring | Remove Comments and Formatting not only deletes all these characters but also removes any text that is located between block comments or after line comments.
The left pane shows a SELECT statement when SQL formatting is enabled in the SQL Formatting options and the Whitespace markers and End-of-Line markers options are activated in the Text View Settings. The right pane shows the same statement with the same options after the Remove Comments and Formatting command has been selected.
If you want to get rid of all that text in your statement that has been commented out, you can use the Remove comments option in the SQL Refactoring menu. Any text that is enclosed by block comment characters or appears after a line comment will be deleted from the active SQL Editor window (see screenshot below).