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 Snippets
Code snippets are frequently used standard pieces of code which you can insert
into your code while working in SQL editor.
Several keystrokes to insert a code snippet
To insert a snippet in SQL document, select it from the suggestion list, use smart
tag, or press the hot key combination Ctr+K and then X. The Insert
Snippet drop-down list appears. Select a snippet and view information about
it. Double-click the required snippet in the list and it will appear in your script.
If snippet has a shortcut word, you can use it for the quickest insertion. Enter
a shortcut word and press the Tab key, the corresponding snippet will be inserted.
Using Snippets Manager
Snippets Manager is used to add new, delete and modify existing snippets. In the
manager, you can add and modify snippet shortcuts, literals and the snippet text
itself.
To open Snippets Manager, click Tools → Snippets Manager in the main menu.
Using the Code Snippets window
Open the Code Snippets 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 Snippets 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 snippets, 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 snippet 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.
Code profiles are used for command standardization. You can create your own profiles with necessary statements to format and adjust their formatting. The application also allows modifying existing profiles.
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.