Inserting suggestions into your code
Published 29 O 2019
SQL Prompt provides SQL code auto-completion using the suggestions box. The suggestions box pops up to display items based on what you type in your query editor.
For example, when you type
SELECT * FROM
, the suggestions box displays a list of database tables, views, owners, databases, functions, and so on.
The suggestions box is displayed automatically when you type in a query window. As you type, the suggestions are filtered to match the typed characters.
To insert the currently highlighted suggestion, press Tab or Enter, or any of the defined insertion keys. You can customize which keys insert suggestions, see Managing SQL Prompt behavior.
Types of suggestions
Suggestions are listed based on the following:
- Prefix suggestions
For example, if you query tables in the AdventureWorks2014 database, typead
, and the Address table is suggested: - CamelCase, or compound word suggestions
For example, if you typebea
, the BusinessEntityAddress table is suggested: - Mid-string suggestions
For example, if you typeen
, the BusinessEntity table is suggested:
Mid-string suggestions are particularly useful if you can only remember part of an object name.
Closing the suggestions box
To close the suggestions box without inserting anything, press the Esc key. If nothing is selected in the suggestions box, you can also press the Enter key to close it.
You can also click anywhere in your query window to close the suggestions box.
Ranked Suggestions
As of SQL Prompt 10.0, Ranked Suggestions are enabled by default and SQL Prompt will attempt to place the most relevant suggestions at the top, using a combination of type, how close the suggestion is to what you have typed, and what suggestions you have previously used. Otherwise, it will sort using a combination of object type and alphabetic sorting.
You can enable/disable ranked suggestions as follows:
- On the SQL Prompt menu, select Options.
- On the Suggestions > Behavior page, under Suggestions box and other popups, check/uncheck the Use ranked suggestions checkbox.
Making the suggestions box semi-transparent
In some cases the suggestions box might be covering up the code you want to see. You can make the suggestions box semi-transparent for as long as you need by pressing and holding the Ctrl key.
You can enable or disable this behavior by doing the following:
- On the SQL Prompt menu, select Options.
- On the Suggestions > Behavior page, under Suggestions box and other popups check/uncheck the Make popups transparent when the Ctrl key is held down option.
Order of suggestions
The order in which suggestions are listed depends on the context of your query. For example:
- After typing
USE
, databases are listed at the top of the suggestions box. - After typing
SELECT * FROM
, tables are listed at the top of the suggestions box, followed by views, schemas and then database names. - In a
CREATE TABLE
statement, after typing a column name, data types are listed at the top of the suggestions box.
Viewing all columns after a SELECT statement
After typing
SELECT
, SQL Prompt doesn't list all the possible columns from all tables by default. If you want to see all column names after typing
SELECT
:
- From the SQL Prompt menu, click Options.
- Go to the Suggestions > Types of suggestion page.
- Select List all database columns after a SELECT statement.
When you type
SELECT
, all column names will now be displayed in the suggestions box, listed alphabetically:
Only displaying the suggestions box manually
To turn off the automatic display of the suggestions box:
- On the SQL Prompt menu, select Options.
- On the Main > Behavior page (displayed by default when you first display the SQL Prompt Options dialog box), clear the Automatically trigger suggestions check box.
When automatic display of the suggestions box is turned off, you can press Ctrl + Space at any time to display it manually. The type of suggestions displayed will depend on where in the query you display the suggestions box.
Using categories
The suggestions box displays items grouped by category. Categories are a way of organizing suggestions so you can find the item you require more easily:
Only the categories that contain suggestions are available to select.
To switch between categories, click the All Suggestions drop-down or press Ctrl + Up arrow or Ctrl + Down arrow.
The following categories are shown:
Tables | Tables from the current database. |
Views | Views from the current database. |
Columns | Column names, with their data types and associated table names (or table aliases). Primary key and foreign key columns are also shown. You can also use the column picker to insert columns. |
Functions | User-defined functions in the current database, and built-in functions. |
Stored Procedures | Stored procedures from the current database. |
Snippets | Shortcuts for inserting pre-defined SQL fragments or statements. For more information about snippets, see Inserting snippets . |
Other Suggestions | Keywords, data types, and objects such as users and roles. |
Object types
You may see the following types of object or suggestion listed in Other Suggestions:
DML triggers | Rules | ||
Users | Defaults | ||
Roles | User defined types | ||
Functions | Full text catalogs | ||
System variables | Join suggestions | ||
Linked server objects |
For SQL Server 2005 and 2008:
Assemblies | Queues | ||
Asymmetric Keys | Routes | ||
Certificates | Schemas | ||
Contracts | Services | ||
DDL Triggers | Service Bindings | ||
Event Notifications | Symmetric Keys | ||
Message Types | Synonyms | ||
Partition Functions | XML Schema Collections | ||
Partition Schemes | Full Text Stoplist (SQL Server 2008 only) |
Displaying schema (owner) names
You can display schema (owner) names in the suggestions box by clicking the right arrow in the bottom left corner of the suggestions box.
The suggestions box is automatically widened to allow space for the schema names, shown in grey to the left of the suggestions:
To hide the list of schema (owner) names, click the left arrow
The arrows only affect how object names are displayed in the suggestions box. The schema (owner) name is only inserted into your code when Qualify object names with owner name is selected on the Inserted Code > Qualification options page.
Changing the size of the suggestions box
You can resize the suggestions box by dragging the resize handle The new size is remembered the next time the suggestions box is displayed.
The size of the suggestions box is remembered between sessions in SQL Server Management Studio and Visual Studio.
Moving through the list of suggestions
You can move through the list of items in the suggestions box by pressing:
- Up / Down arrow keys to move up or down the list one item at a time. If you are at the top of the list, pressing the Up arrow key takes you to the bottom of the list; if you are at the bottom of the list, pressing the Down arrow key takes you to the top of the list.
- Page Up or Page Down to move up or down the list one page at a time.
- Ctrl + Page Up or Ctrl + Page Down to move up or down the list one page at a time.
Changing how suggestions are inserted
When you select an item from the suggestions box, SQL Prompt inserts the suggestion according to the options you have set in the Inserted code pages of the Options dialog box:
For example, you can specify how ALTER and INSERT statements are inserted, and whether object names are qualified. For more information, see Customizing inserted code.
Encrypted objects
SQL Prompt can automatically decrypt encrypted objects to show their creation script in the object definition box.
If you do not have permission to decrypt objects, you can still view them in the suggestions box and insert them into your query.
By default, SQL Prompt will decrypt encrypted objects. To turn this option off:
- On the SQL Prompt menu, select Options.
- On the Suggestions > Behavior page, under Encrypted objects, clear the Decrypt encrypted objects check box.
Expanding SELECT * to insert all columns
When you use a * wildcard in your query, you can expand it to explicitly list all the columns in the table or view.
Press Tab after the * wildcard to insert all the columns. The columns are formatted based on the settings in the Format pages in the Options dialog box.