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 tables first, then views, and further down the list, 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 in the following order:

1. Prefix suggestions
   For example, if you query tables in the AdventureWorks2014 database, type ad , and the Address table is suggested:
   
2. CamelCase, or compound word suggestions
   For example, if you type bea , the BusinessEntityAddress table is suggested:
   
3. Mid-string suggestions
   For example, if you type en , 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.

Suggestions mode

SQL Prompt will suggest syntax for you as you type. There are three different modes that can be used:

Standard will sort using a combination of object type and alphabetic sorting.

Recently used will place the suggestions that you have most recently used at the top of the suggestions box, separated from the usual suggestions by a grey line.

Only certain object types appear as recently used suggestions, and will be persisted only while SQL Server Management Studio is open.

Ranked 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.

You can change the suggestions mode by doing the following:

1. On the SQL Prompt menu, select Options.
2. On the Suggestions > Behavior page, under Suggestions box and other popups, change the ordering mode option.

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:

1. On the SQL Prompt menu, select Options.
2. 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 :

1. From the SQL Prompt menu, click Options.
2. Go to the Suggestions > Types of suggestion page.
3. 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:

1. On the SQL Prompt menu, select Options.
2. 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  

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:

1. On the SQL Prompt menu, select Options.
2. 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.