Inserting suggestions into your code

SQL Prompt - 5.0

Versions available: 5.3, 5.2, 5.1, 5.0, 4.0

Learning SQL Prompt - 5.0

Inserting suggestions into your code

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.

suggestionsbox

The suggestions box is displayed automatically when you type the first character of a keyword or identifier. As you type, the suggestions are filtered to match the typed characters:

To insert the currently highlighted suggestion, press the Tab or Enter key, or any of the defined insertion keys. You can customize which keys insert suggestions. See Setting main behavior options.

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 Return or Enter key to close it.

You can also click anywhere in your query window to close the suggestions box.

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 does not by default list all the possible columns from all tables. 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:

Selectallcolumns

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 > Behaviour 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 bar 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:

AllSuggestions

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 lists tables from the current database.
	Views lists views from the current database
	Columns lists 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 lists user-defined functions in the current database, and built-in functions.
	Stored Procedures lists stored procedures from the current database.
	Snippets lists shortcuts for inserting pre-defined SQL fragments or statements. For more information about snippets, see Inserting snippets.
	Other Suggestions lists 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

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.

ShowSchemaNames

The suggestions box is autmomatically widened to allow space for the schema names, shown in grey to the left of the suggestions:

SuggestionsWithSchemas

To hide the list of schema (owner) names, click the left arrow

Note: 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 SSMS 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:

OptionsDialogInsertedCode

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. (Available in SQL Prompt Pro Edition only)

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.
Go to the Suggestions > Types of suggestion page.
Turn off Decrypt encrypted objects.

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.

Was this article helpful?

Search support