dbForge Studio: Enhanced SQL Management
Contents
This article describes SQL text-editing features provided in dbForge
Studio for MySQL and dbForge Fusion for MySQL.
You can effortlessly create and then edit SQL scripts using SQL editor. Press the
New
SQL icon on the Standard toolbar to create a new SQL script. To open existing
SQL scripts, in the top menu select File→Open File and then choose the
required file.
Code Templates library with a browser window
Code templates are frequently used code snippets which you can insert into
your code while working in SQL editor. You can quickly access code templates
in two ways (both of them are simple): pressing the shortcut in SQL editor or using
the Code Templates window.
Several keystrokes to insert a code snippet
Open the SQL script where you want to insert a code template and press the hot key
combination Ctr+K and then X. The Insert Snippet drop-down
list appears. Double-click the required snippet in the list and it will appear in
your script.
Most snippets have a shortcut word (you can see it in the tooltip, when the snippet
is selected), which is used for the quickest insertion. Enter a shortcut word and
press the Tab key, the corresponding snippet will be inserted.
Using the Code Templates window
Open the Code Templates window and drag-and-drop the required snippet to
the opened SQL script or right-click the snippet and select the Insert into Document
option from the menu.
By default, the Code Templates window is docked to the left upper corner
of the program window and is in auto-hide mode, so it is easy to see and quickly
open it while working with SQL scripts. In the window you can see a set of folders
with neatly organized code templates and a preview panel. Just click, preview, and
then insert. Based on your needs, you can rename code templates, add new ones, and
rearrange all by drag-and-dropping in the window.
The variables and parameter values in the code templates are highlighted with green.
This feature is made not only for better visibility of parameters, but also for
quick navigating through them. Insert the template into the script, enter the required
parameters into the green fields and press the Tab key to move from one variable
to another. To complete the insertion, press Enter. Note, when you press
Enter, you can't navigate through the inserted parameters any more.
Context-sensitive Code Completion
When typing code, you can benefit from several code completion features. They are
the following:
-
Word Completion
-
List Members
-
Parameter Information for stored procedures
-
Quick Information about schema objects
Depending on what code elements you are typing, the automatically appeared completion
list will show the best matches. Select the required value from the list and press
Enter. To close the completion list, press Esc. You can easily access
the completion features either selecting in the top menu Edit→Code Completion
or pressing corresponding icons on the Text toolbar. You will quickly remember which
icon to choose thanks to the tooltips.
Type just one or two first characters of any item in SQL syntax and the Word Completion
feature opens the completion list where the most appropriate match is highlighted.
Next to the list you can see a brief explanation for the selected item.
When typing names of valid databases, tables, columns, and other database objects,
the List Members feature helps you complete them and shows the matches in
the completion list. In case you have typed, for example, the table name and then
the dot (.) symbol, the completion list will show all columns names of this
table. You can also open the List Members completion list, by pressing the familiar
shortcut - Ctrl+Space.
The Parameter Information feature will help you quickly see the names and
types of parameters, which are used in a function or stored procedure script. All
information is neatly displayed in the Parameter Info tooltip. The parameter
in bold is the next parameter that is required for the function or stored procedure.
To see the information about procedure or function parameters, type the name of
the procedure (or function) and open the left bracket. To see the tooltip again,
press the familiar shortcut Ctrl+Shift+Space.
The Quick Info feature is a tooltip with brief information about database
objects, parameters, and variables. The tooltip appears when you place the mouse
pointer on the required item in the SQL script or press the shortcut Ctrl+K
and then I. In case of database objects, the tooltip shows their location
and types. You will find this option useful, while working with large scripts.
Automatic SQL syntax check
Syntax check is a great option, as it helps you create error-free SQL code
and significantly save time while checking the code. As you start typing, syntax
check automatically checks any element in your code. If any errors are found, they
are highlighted with red wavy lines. If you place the mouse pointer to any underlined
error, you can see the error text in the tooltip.
One-click access to schema objects' definitions
Working with scripts, you can navigate to definitions of schema objects and variables
in one click. Right-click an object or a variable in your code and select the Go
to definition option from the menu. The mouse pointer will be moved to the
object's definition.
If the definition is outside of the current script, an object editor or a corresponding
DDL script will open in another window. You will appreciate this feature even more
while working with SQL scripts in a database project. A project can contain SQL
scripts and query files located in different folders and on different discs, but
thanks to Go to definition option you still can navigate to the definitions
of database objects from the scripts where these objects are mentioned.
On-the-fly renaming of database objects
Renaming of database objects requires accuracy and time. In SQL editor you can quickly
rename as well as preview the changes before applying them. Save your efforts and
rename the following database objects: tables, columns of tables, views, aliases,
stored routines, local variables, triggers, events, UDFs, and users.
To rename an object in the script, right-click it and select the Rename option
from the menu. The Rename dialog window opens where you should enter a new
object's name. If required, you can unselect the checkboxes next to the following
renaming options (to disable them):
-
Preview renaming changes
-
Search in strings
-
Search in comments
To finish renaming and preview the changes in the Preview Changes dialog
box, click OK.
The dialog box organizes all the changes as a tree in the upper part and allows
you to preview changes, marked with green, in the bottom part.
The tree contains 3 main nodes:
-
Top node - includes a database object and all references.
-
Strings - contains found entries in text strings.
-
Comments - contains found entries in comments.
By default, the check boxes next to these nodes are selected, you can unselect them
and renaming changes will not be applied to them. Click any change in the tree to
see it in the Preview code changes box, under the tree. To apply the changes, click
the Apply button at the bottom of the Preview Changes dialog box. To discard
changes and close the dialog box, click Cancel.
Extended options for Code Formatting
We format our code to make it standards-driven and clear for other people.
dbForge Studio for MySQL and dbForge Fusion for
MySQL have a wide choice of options for code formatting. Based on your needs,
you can use 3 types of formatting:
-
Automatic
-
Manual
-
Using SQL formatter wizard - to format multiple SQL files at once.
Automatic code formatting significantly saves your time. By default, when
you finish a statement with a delimiter, it is automatically formatted. You can
tune automatic formatting and change the default rules in the Preferences
window. In the top menu select Tools→Options, when the window opens,
expand the Formatting node, select the required tab, and edit the rules.
The New Lines, Indentation, Spacing, and Wrapping tabs allow you to preview the
selected rule. To apply the rules, click OK.
In case you want to format SQL code manually, decide whether you want to
format the whole code in your script or only a statement. To format the whole script,
in the top menu select Edit→Advanced→Format Document. To format
the current statement, right-click the required statement and select the Format
Current Statement option from the menu. You can also select one or several
statements and choose the Format Selection option from the right-click menu.
The selected code will be formatted.
Use SQL Formatter wizard to format SQL code in multiple files or folders.
To open the wizard, in the top menu select Tools→SQL Formatter. On the
first wizard's page select whether to format only selected SQL files or files in
the selected folders. Move to the next page to select either files or folders. (In
our case the files were selected.) To add folders to the list or remove them, use
the corresponding buttons in the right corner of the wizard.
To start formatting, click the Format button. When formatting is finished,
check the results in the selected files, which are automatically opened.
Summary
Nowadays, contemporary tasks require more and more sophisticated work with SQL scripts,
so we have to speed up our work, automate coding, and produce standards-driven code.
dbForge Studio for MySQL and
dbForge Fusion for MySQL have all essential features to help you with creating
and editing SQL scripts. You can use code templates to quickly create the required
statement, significantly save time using code completion features, and entrust checking
errors to automatic syntax check. Renaming of objects or local variables is no longer
a time-consuming and troublesome task, but quick and visual with the Renaming feature.
Add to your benefits a quick navigation to definitions of database objects and rich
options for code formatting.
Make maximum use of SQL text-editing features and produce high-quality SQL code.