Advanced features: Code Snippets, SQL Syntax Checking and Code Formatting

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 File menu, click 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 a 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 Snippets Manager in the Tools 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
Phrase 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 by either selecting Code Completion from the Edit menu, or by 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, table columns, views, aliases, stored routines, local variables, triggers, events, UDFs, and users.

To rename an object in the script:

Right-click an object name.
Click Rename on the shortcut menu.
Enter a new name for the object.
Press F2 to preview changes, or press Enter to complete object renaming without preview.

The Preview Changes 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. 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 4 types of formatting:

Automatic
Manual
Using SQL formatter wizard - to format multiple SQL files at once.
Through the command line (not available in dbForge Fusion for MySQL)

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 Tools menu, click 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 Edit menu, point to Advanced, and then click 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, click SQL Formatter in the Tools menu. 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.

You can perform all code formatting tasks, including bulk formatting and scheduling, through the convenient command-line interface. To lean more, refer to the Formatting Code through the Command Line entry of the product documentation.

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.