Formatting SQL

www.altova.com すべてを展開/折りたたむ このトピックを印刷 前のページ 1つ上のレベル 次のページ

ホーム >  SQL との作業 >

Formatting SQL

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 ダイアログボックス, you can furthermore disable this formatting for the SQL エディター and/or the データベース構造変更スクリプト window. Both チェックボックスes are activated by default.

 

Any changes you make to the default formatting are recorded in a configuration file, at the following path: ..\Documents\Altova\DatabaseSpy2019\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.

 

メモ: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 (次を参照: SQL ステートメントの生成). 更に、 you can always click the Apply SQL Formatting to the active SQL エディター ic_apply-sql-formatting ボタン.

 

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
Firebird
Progress OpenEdge
Any Other Database (ANSI 2003 syntax)

 

The SQL Formatting Configurations ダイアログボックス 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.

ds_dlg_sql_formatting

Preview グループボックス内で、 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 グループボックス.

 

Options on root level

If you select ルートレベル 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.メニューオプション 「ツール | オプション」 を選択、または、「Ctrl+Alt+O」を押しオプションダイアログボックスを開きます。  to open the Options ダイアログボックス and click the SQL Formatting Configurations... ボタン on the SQL Formatting page.
2.SQL Formatting Configurations ダイアログボックス内で、 select the 個々の database kind from the ドロップダウンリスト in the Configurations グループボックス.
3.オプションセクション内で、 select the Ignore All チェックボックス.

 

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 ダイアログボックス. Different options are available for the individual statement types.

 

The header of the options list provides an アイコン on the right edge that displays the properties inherited from ルートレベル. If you click the Show inherited properties ic_show-inherited-properties アイコン, the options from ルートレベル 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 ルートレベル 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 ic_hide-inherited-properties ボタン.

 

You can customize the display of the following statement types (click a statement type to view the formatting options applicable to it):

 

Select

Statement

New line before ORDER BY

Select Items List

First item on new line

Subsequent select list items on new lines

FROM Clause

New line before FROM

First table source on new line

Subsequent table sources on new lines

WHERE Clause

New line before WHERE

First search condition on new line

Subsequent search conditions on new lines

Create Table

Statement

First column/key/index/etc. on new line

Subsequent columns/keys/indices/etc. on new lines

New line before the テーブル定義

Create Function

Statement

Indent contents

Alter Table

Statement

New line before (ADD) CONSTRAINT

New line before 外部キー

New line before REFERENCES

New line before PRIMARY KEY

New line before UNIQUE

Alter Function

Statement

Place BEGIN and END on new lines

Indent contents

Update

Columns List

New line before columns list

First column on new line

Subsequent columns on new line

WHERE Clause

New line before WHERE

First search condition on new line

Subsequent search conditions on new lines

Insert

Columns List

New line before columns list

First column on new line

Subsequent columns on new line

Values List

New line before values list

First value on new line

Subsequent values on new line

Create Procedure

General Formatting

Disable all formatting

New line before definition

Create Trigger

General Formatting

Disable all formatting

New line before definition

Alter Procedure

General Formatting

Disable all formatting

New line before definition

Create View

General Formatting

Disable all formatting

New line before definition

Alter View

General Formatting

Disable all formatting

New line before definition

 

メモ:Some statement types listed above are not available for configuration when they are not applicable to the selected database kind.

 

Removing formatting and comments

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. メニューオプション 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.

sql_format-flatten

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 テキストビュー設定. The right pane shows the same statement with the same options after the Remove Comments and Formatting command has been selected.

 

Removing comments

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 メニュー。 Any text that is enclosed by block comment characters or appears after a line comment will be deleted from the active SQL エディターウィンドウ (下のスクリーンショットを参照してください)。

sql_remove-comments


(C) 2019 Altova GmbH