dbForge Studio: Enhanced SQL Management

Contents

• Code Templates library with a browser window
• Context-sensitive Code Completion
• Automatic SQL syntax check
• One-click access to schema objects' definitions
• On-the-fly renaming of database objects
• Extended options for Code formatting
• Summary

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 Inset 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.