Auto-Completion

Aqua Data Studio supports auto-completion for database tables and columns. As the user type SQL statements, the editor will analyze the SQL statement and determine whether the user can be assisted by a popup menu of options. How quickly the auto-completion popup menu appears, and how complete its listings are, can also be set with a series of options.

The editor will identify the list of tables and columns from the Database and prompt the user depending on the position of the cursor. If the editor does not prompt a user for options with a popup menu, the user may always request "options'' with the CTRL+Space hot-key. Auto-completion via CTRL+Space may not provide options immediately following SELECT because the syntax for a select is SELECT <column name> FROM <table name>. For auto-completion to determine the column name it first needs the table name.

While writing SQL statements the user must be aware of separating statements with the appropriate statement separator (use only "GO" or "/") so that the editor may appropriately analyze the current statement. Having multiple statements not separated by statement separators will prevent auto-completion from providing the correct tables and columns.

The editor toolbar (Query Analyzer Toolbar) has 3 options that control the behavior of "auto-completion." Their icons are found on the second row of the Query Analyzer Toolbar, 11 icons to the right, just after "reconnect".  The options are Auto-Complete, Auto-on-all-schemas, and, refresh auto-completion.

The "Auto-Complete" button will enable or disable auto-completion for the query window. "Auto-on-all-schemas" determines whether auto-completion displays a list of tables in the current user schema or all tables in all user schemas in the connected database. The user may always get a list of tables in a specific schema by specifying the schema name and "." (eg HR.^).

The editor will cache the schema tables and columns for auto-completion for performance purposes. Any table additions or removals after the query window is open, will not be available to auto-completion. You must press the "Refresh Schema" button to clear the cache.

Abbreviation Auto-completion - Auto-completion also works with abbreviations that a user has created for each of the editors (File > Options > Editors > SQL Editor > Abbreviation.) If the text that begins an abbreviation is typed, Aqua Data Studio will prompt the user with a popup containing the contents of the abbreviation. Clicking on this prompt will complete it, as will typing the abbreviations trigger keystroke. Large blocks of text can be set from an abbreviation, creating a customized auto-completion prompt.

Cross Database Auto-completion - Auto-completion for SQL Server, Sybase, MySQL, and Informix has support for completion across databases. Indicating a table that is in a database other than the current database will provide column completion for the correct table. Autocompleting for tables in databases other than the current one will also complete correctly. Below are some examples:

Current Database: master

Query: select * from Northwind.dbo.^Customers cu left join Example_Data.dbo.^Employees e on cu.^CustomerID = e.^EmployeeID

Completion in the locations marked with "^" will complete with the appropriate tables for the database, or the appropriate columns for the matching table.

Global Auto-completion Cache - The Global Auto-Completion Cache provides an option to allow the sharing of Auto-Completion schema data across different query windows. When this option is enabled and you open 10 query windows for one registered database server, the Auto-Completion schema will be stored only once and shared across all the query windows.