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.

Suggestion box

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

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:

  1. Go to the SQL Complete menu, select Options.
  2. 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.

Prefix suggestions

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.

CamelCase suggestions

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.

Mid-string suggestions

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.

Order

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.

Schemas

Columns are sorted by the order they were created when the table was formed.

Columns

Objects inside their groups are sorted alphabetically.

Objects

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.

Changing the way

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 Aliases for table objects (tables, views, functions, synonyms, sub-queries)
Built-in data types Built-in data types
Built-in functions Built-in functions
Check constraints Check constraints
Collations Collations
Column master keys Column master keys Column master keys (described from a database or a script respectively)
Columns classified with the critical sensitivity rank Columns classified with the critical sensitivity rank
Columns classified with the high sensitivity rank Columns classified with the high sensitivity rank
Columns classified with the medium sensitivity rank Columns classified with the medium sensitivity rank
Columns classified with the low sensitivity rank Columns classified with the low sensitivity rank
Columns without any sensitivity rank assigned Columns without any sensitivity rank assigned
Columns of table objects Columns of table objects Columns of table objects (described from a database or a script respectively)
Columns with a primary key Columns with a primary key Columns with a primary key (described from a database or a script respectively)
Columns with a unique key Columns with a unique key Columns with a unique key (described from a database or a script respectively)
Columns with primary and foreign keys Columns with primary and foreign keys
Contracts Contracts Contracts (described from a database or a script respectively)
Cursors Cursors
Database-level triggers Database-level triggers Database-level triggers (described from a database or a script respectively)
Databases Databases Databases (described from a server or a script respectively)
Default constraints Default constraints
Defaults Defaults
Encrypted database-level triggers Encrypted database-level triggers
Encrypted scalar functions Encrypted scalar functions
Encrypted server-level triggers Encrypted server-level triggers
Encrypted table-level triggers Encrypted table-level triggers
Encrypted table-valued functions Encrypted table-valued (including CLR) functions
Encrypted views Encrypted views
Filegroups Filegroups
Foreign keys Foreign keys
Full text stop lists Full text stop lists
Global system variables Global system variables
IN parameters for procedures and functions IN parameters for procedures and functions IN parameters for procedures and functions (described from a database or a script respectively)
Indexes Indexes
Labels Labels
Languages Languages
Linked servers Linked servers
Local variables Local variables
Logins Logins
Message types Message types
OUT parameters for procedures and functions OUT parameters for procedures and functions OUT parameters for procedures and functions (described from a database or a script respectively)
Primary keys Primary keys
Procedures Procedures Procedures (described from a database or a script respectively)
Queues Queues Queues (described from a database or a script respectively)
Roles Roles
Routes Routes Routes (described from a database or a script respectively)
Rules Rules Rules (described from a database or a script respectively)
Scalar functions Scalar functions Scalar functions (described from a database or a script respectively)
Schemas Schemas
Sequences Sequences Sequences (described from a database or a script respectively)
Server-level triggers Server-level triggers Server-level triggers (described from a server or a script respectively)
Servers Servers
Services Services Services (described from a database or a script respectively)
Snippets Snippets
Suggested conditions after the ON keyword Suggested conditions after the ON keyword
Suggested conditions for JOIN statements Suggested conditions for JOIN statements
Suggested keywords Suggested keywords
Synonyms for supported objects Synonyms for supported objects Synonyms for supported objects (described from a database or a script respectively)
Table-level triggers Table-level triggers Table-level triggers (described from a database or a script respectively)
Table-valued functions Table-valued functions Table-valued (including CLR) functions (described from a database or a script respectively)
Tables Tables Tables (described from a database or a script respectively)
Transactions Transactions
User-defined data types User-defined data types User-defined data types (described from a database or a script respectively)
User-defined table types User-defined table types User-defined table types (described from a database or a script respectively)
User-defined types User-defined types
Users Users
Views Views Views (described from a database or a script respectively)
XML indexes XML indexes
XML Schema Collections 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.

OUTER APPLY

The ON NULL attribute of a created scalar-valued function is prompted.

ON NULL

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.

Search conditions

Full JOIN statements suggestion

SQL Complete suggests a full JOIN statement that links the specified tables, including the JOIN condition.

JOIN operations

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.

SQLCMD mode

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.