Insert suggestions into code
dbForge SQL Complete provides SQL autocompletion using the suggestion box. The suggestion box pops up to display items based on the current context of a query that is being typed in the SQL editor.
For example, when you type SELECT * FROM, the suggestion box displays a list of database tables, views, schemas, databases, functions, etc.
The suggestion box is displayed automatically when you type in a SQL document. As you type, the suggestions are filtered to match the characters you entered.
To insert a currently highlighted suggestion, press Tab or Enter. To select the keys that insert suggestions, go to SQL Complete Options > List Members > Commit Selection options group.
This topic describes the following features:
- Invoke and close the suggestion box
- Move through the list of suggestions
- Change the size of the suggestion box
- Make the suggestion box semi-transparent
- Suggestion filtering and selections
- Order of suggestions
- Change the way suggestions are inserted
- Encrypted objects
- Prompted object types
- Context-based suggestions
- Word autocompletion
- Phrase suggestions
- JOIN suggestions
- Suggestions in the SQLCMD mode
- Exclude databases from suggestions
Invoke and close the suggestion box
Start typing a query in a SQL document, and the suggestion box will pop up automatically. To invoke it manually, simply press Ctrl+Space.
To close the suggestion box without inserting anything, press Esc. If nothing is selected in the suggestion box, press Enter to close it. The suggestion box will also close if you click anywhere in the SQL Document window.
Move through the list of suggestions
You can move through the list of items in the suggestion box using the following keys:
-
Use Up and Down arrow keys to move up or down the list one item at a time. If the Cyclic movement through the suggestion list option is enabled, pressing the Up arrow key will take you from the top of the list to the bottom of the list. Similarly, if you are at the bottom of the list, pressing the Down arrow key will take you to the top of the list.
-
Use Page Up and Page Down to move one page up or down.
-
Use Ctrl+Page Up and Ctrl+Page Down to move to the top or bottom of the list.
-
In some queries, use the Left and Right arrow keys to show the column list.
-
Use the Space key to select columns in the column picker. Keep in mind that this works only if you have not entered a single character to find the column name.
-
Use the Insert key to select or cancel the selection of columns regardless of whether the column name has been fully or partially entered.
Note
The Cyclic movement through the suggestion list option is disabled by default. It can be enabled as follows:
- Go to the SQL Complete menu, select Options.
- On the List Members tab, select the Cyclic movement through the suggestion list checkbox.
Change the size of the suggestion box
Drag a border of the suggestion box to resize it. Next time you open the suggestion box, it will keep the size you set up. This feature works both in SQL Server Management Studio and Visual Studio.
Make the suggestion box semi-transparent
Sometimes the code you need to see is hidden behind the suggestion box. For such cases, you can make the suggestion box semi-transparent by pressing and holding the Ctrl key.
Suggestion filtering and selections
As you type the first letters of the object name, SQL Complete prompts a list of available objects and automatically filters them depending on the characters you are entering.
Types of suggestions
Prefix suggestions
When you query AdventureWorks2019 database tables and type add, the objects in the suggestion box will be filtered to include those which names begin with or contain the characters you entered. In the list of filtered suggestions, prefix matches come first.
CamelCase suggestions
When you query AdventureWorks2019 database tables and type jc, the JobCandidate table will be suggested. CamelCaps suggestions allow filtering suggestions by first letters of compound names. The v, tmp, prc, and fnc prefixes are ignored. In the long list of filtered suggestions, CamelHumps matches come after prefix matches.
Mid-string suggestions
When you query AdventureWorks2019 database tables and type ent, SQL Complete will filter the objects in the suggestion box to include those that contain the characters you entered. In the list of filtered suggestions, prefix matches come first, then CamelCase matches, and mid-string matches in the end.
Note
As you can see in the screenshots above, one of the items in the filtered suggestions list is marked as selected. SQL Complete considers it to be the best match.
The selection priority is as follows:
- Prefix matches in alphabetical order
- CamelCase matches in alphabetical order
- Mid-string matches ordered so that the matches closest to the beginning of a name come first
Order of suggestions
The order in which suggestions are listed depends on the context of the query.
By default, the keywords are sorted by relevance.
You can enable or disable this behavior as follows:
1. Go to the SQL Complete menu, select Options.
2. On the List Members tab, select or clear the Sort keywords by relevance check box.
Object types in the suggestions list are sorted by relevance. The suggestions list changes according to the query context.
For example, in the SELECT queries the list of suggestions first shows tables and views, then schemas, then databases, then linked servers.
However, if a database has already been selected, the list will show tables, views, and schemas. When selecting a table or a view, the schema to which the table of view belongs is automatically added to the query. After you select a table or view, the schema to which the table or view belongs will be automatically added to the query.
Columns are sorted by the order they were created when the table was formed.
Objects inside their groups are sorted alphabetically.
Change the way suggestions are inserted
When you select an item from the suggestion box, SQL Complete inserts the suggestion according to the rules set on the Advanced tab of the Options dialog box.
For instance, you can specify how the INSERT and ALTER statements should be inserted, and whether to qualify object names or not.
Encrypted objects
SQL Complete can automatically decrypt encrypted objects to show their creation script in the object definition box.
If a user doesn’t have permission to decrypt objects, they can still view encrypted objects in the suggestion box and insert them into queries.
By default, SQL Complete will decrypt encrypted objects. To turn this option off:
1. Go to the SQL Complete menu, select Options.
2. On the Advanced tab, clear the Decrypt encrypted objects when opening check box.
Prompted object types
Depending on the context, SQL Complete can show the following object types on the suggestions list:
 |
Aliases for table objects (tables, views, functions, synonyms, sub-queries) |
 |
Built-in data types |
 |
Built-in functions |
 |
Check constraints |
 |
Collations |
  |
Column master keys (described from a database or a script respectively) |
 |
Columns classified with the critical sensitivity rank |
 |
Columns classified with the high sensitivity rank |
 |
Columns classified with the medium sensitivity rank |
 |
Columns classified with the low sensitivity rank |
 |
Columns without any sensitivity rank assigned |
  |
Columns of table objects (described from a database or a script respectively) |
  |
Columns with a primary key (described from a database or a script respectively) |
  |
Columns with a unique key (described from a database or a script respectively) |
 |
Columns with primary and foreign keys |
  |
Contracts (described from a database or a script respectively) |
 |
Cursors |
  |
Database-level triggers (described from a database or a script respectively) |
  |
Databases (described from a server or a script respectively) |
 |
Default constraints |
 |
Defaults |
 |
Encrypted database-level triggers |
 |
Encrypted scalar functions |
 |
Encrypted server-level triggers |
 |
Encrypted table-level triggers |
 |
Encrypted table-valued (including CLR) functions |
 |
Encrypted views |
 |
Filegroups |
 |
Foreign keys |
 |
Full text stop lists |
 |
Global system variables |
  |
IN parameters for procedures and functions (described from a database or a script respectively) |
 |
Indexes |
 |
Labels |
 |
Languages |
 |
Linked servers |
 |
Local variables |
 |
Logins |
 |
Message types |
  |
OUT parameters for procedures and functions (described from a database or a script respectively) |
 |
Primary keys |
  |
Procedures (described from a database or a script respectively) |
  |
Queues (described from a database or a script respectively) |
 |
Roles |
  |
Routes (described from a database or a script respectively) |
  |
Rules (described from a database or a script respectively) |
  |
Scalar functions (described from a database or a script respectively) |
 |
Schemas |
  |
Sequences (described from a database or a script respectively) |
  |
Server-level triggers (described from a server or a script respectively) |
 |
Servers |
  |
Services (described from a database or a script respectively) |
 |
Snippets |
 |
Suggested conditions after the ON keyword |
 |
Suggested conditions for JOIN statements |
 |
Suggested keywords |
  |
Synonyms for supported objects (described from a database or a script respectively) |
  |
Table-level triggers (described from a database or a script respectively) |
  |
Table-valued (including CLR) functions (described from a database or a script respectively) |
  |
Tables (described from a database or a script respectively) |
 |
Transactions |
  |
User-defined data types (described from a database or a script respectively) |
  |
User-defined table types (described from a database or a script respectively) |
 |
User-defined types |
 |
Users |
  |
Views (described from a database or a script respectively) |
 |
XML indexes |
 |
XML Schema Collections |
Context-based suggestions
The content of the suggestions list depends on the context that invokes the suggestion. To learn more about it, please visit our T-SQL Context Prompt page.
Note
The list of the supported context prompts provided on the page is not exhaustive. The dbForge team is continuously improving the product to support more context prompts.
Word autocompletion
When the first several characters of a database object name or a keyword are being typed, and SQL Complete finds only one match, it automatically inserts it to complete the word.
For instance, if the following statement is entered when the completion list is closed:
SELECT * FROM [AdventureWorks2019].[Sales].[vIndividual
Pressing Ctrl+Space will insert the following:
SELECT * FROM [AdventureWorks2019].[Sales].[vIndividualCustomer]
And if we enter this while the completion list is closed:
SELECT * FROM [AdventureWorks2019].[Sales].[CurrencyRate] [cr] jo
Pressing Ctrl+Space will insert the following:
SELECT * FROM [AdventureWorks2019].[Sales].[CurrencyRate] [cr] JOIN
Phrase suggestions
SQL Complete suggests not only separate keywords and object names, but also entire code phrases.
For example:
OUTER APPLY instead of APPLY and ORDER BY instead of ORDER are prompted in the SELECT query.
The ON NULL attribute of a created scalar-valued function is prompted.
JOIN suggestions
SQL Complete suggests a complete JOIN statement when you combine tables based on foreign keys or it can prompt conditions based on column names. This means that the tool scans column names and traces primary and foreign key relationships on the tables you are joining.
Search conditions suggestion
SQL Complete can prompt join conditions. It does so based on the foreign key between the columns as well as on the coinciding column names and data types in both tables. Please take note that the key icon shows that the suggestion has been generated based on the foreign key.
Full JOIN statements suggestion
SQL Complete suggests a full JOIN statement that links the specified tables, including the JOIN condition.
Refer to the description of JOINS on the Types of SQL JOINS Explained with Examples page.
Suggestions in the SQLCMD mode
This feature ensures that the values of variables are taken from the document, and object suggestions work as with an ordinary SQL script, when scripts are executed using the command line utility.
In order to get prompts, it is necessary to enable the SQLCMD mode on the Query menu.
Exclude databases from suggestions
To speed up the loading of the suggestions list and dispose of the extraneous databases and their objects, it is possible to exclude those databases from the suggestions list. This can be done as follows:
1. Go to the SQL Complete menu > Options > Suggestions
2. In the Suggestions options group, select the Do not load suggestions for the following databases check box and specify the required databases. Use the ; character as a delimiter when specifying multiple databases.
In the same window, you can set the local cache to be refreshed automatically for a specified period (e.g. every 3 minutes). You can also enable the Detect changes on a server before refreshing local cache option, if necessary.
Refresh suggestions
If you are writing a query on a database while someone else is making changes to the structure of that database, the suggestions offered by SQL Complete may become outdated. For instance, if someone has added several tables to a database while you have been composing a query, these tables will not appear in the suggestions box. To reload the suggestions, click Refresh Suggestions. Refreshing suggestions applies only to the database to which you are currently connected.
Want to Find out More?
Overview
All features
Request a demo
