Using the Candidate List

SQL Prompt provides assistance for SQL code completion with a candidate list. The candidate list displays items based on the context of what you type in your query editor. For example, when you type SELECT * FROM the candidate list displays a list of tables and views at the top of the list.

You can customize the appearance and behavior of the candidate list in your SQL Prompt options; for more information about the Options dialog box, see Options.

Some examples of how you use the candidate list are provided in Worked Examples.

Displaying the candidate list

You can display the candidate list:

automatically as you type
SQL Prompt displays the candidate list automatically when you type the first character of a keyword or identifier.

SQL Prompt also displays the candidate list automatically when you finish typing a trigger word and press SPACEBAR. Some trigger words are already defined for you, but if required, you can specify additional trigger words on the Pop-up Behavior, Triggering options page.

You can specify how long SQL Prompt will wait before displaying the candidate list. If you do not want the candidate list to display automatically, you can switch off the automatic display.
at any time, by pressing CTRL+SPACEBAR

To close the candidate list, press ESC.

Using categories

The candidate list displays items grouped by category. The categories are a way of filtering the candidates list so that you can find the item you require more easily. To switch between categories:

click the category icon at the bottom of the candidate list
or press CTRL+LEFT ARROW and CTRL+RIGHT ARROW
If required, you can choose to use SHIFT+LEFT ARROW and SHIFT+RIGHT ARROW to switch between categories, using the Pop-up Behavior, Category Navigation options page. You can also turn off this feature if necessary; for example, you may want to do this if you use these shortcut keys for a different purpose.

Note that if there are no candidates to display for a particular category, you cannot select that category.

The following categories are shown:

Snippets lists shortcuts for pre-defined SQL fragments or statements. If an insertion point has been defined, when you insert the snippet, SQL Prompt places the cursor at the relevant position in your query editor. For more information about snippets, see Managing Snippets.

Column picker lists column names, with associated data types and table names (or table aliases), for tables you have specified in a SELECT statement or for a table you have specified in an INSERT statement.

For details of how you select the columns, see Using the column picker.

Suggested candidates is the default category. The candidates displayed in this category depend on the context of what you type in your query editor.

The following types of candidate may be listed:

database names	database objects (for a full list, see Object types)
linked servers	keywords (displayed in blue or gray)
data types	built-in functions (displayed in pink)
join conditions
local variables

By default, SQL Prompt lists exact matches at the top of the candidate list irrespective of their type, and the remaining candidates are grouped by type, and then sorted alphanumerically within each group; you can turn off exact matches on the Listed Candidates, Candidate Ordering options page.

Tables lists tables from the current database.

System tables are displayed if you have chosen to display system objects.

Columns lists column names, with their data types and associated table names (or table aliases). Primary key and foreign key columns are also shown.

Views lists views from the current database.

System views are listed if you have chosen to display system objects.

Stored procedures lists stored procedures from the current database.

System stored procedures are listed if you have chosen to display system objects.

Functions lists user-defined functions from the current database, and built-in functions.

System functions are displayed if you have chosen to display system objects.

Other candidates lists keywords, data types, and objects such as users and roles. For a full list of object types, see Object types.

Object types

You may see the following types of object listed on the Suggested candidates and Other candidates categories:

	Tables		Rules
	Views		Defaults
	Stored Procedures		User Defined Types
	Users		Functions
	Roles		Full Text Catalogs
	DML Triggers

For SQL Server 2005:

	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

System objects

By default, SQL Prompt does not display system objects in candidate lists. If you want to display system objects, on the Options dialog box, click the Listed Candidates tab, click the Candidate Types and Filters page, and then select the System objects check box.

Schema (owner) names

You can display schema (owner) names in the candidate list by clicking the left arrow button in the lower left corner of the candidate list.

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

Note that when you select a candidate, the schema (owner) name is inserted in your query editor only if Qualify object names is selected on the Inserted Candidates, Formatting options page.

Customizing the candidate list

You can resize the candidate list by dragging the resize handle or by specifying the height and width on the Pop-up Behavior, Candidate List Size and Font options page.

You can also change the font used in the candidate list on this options page. (This also changes the font used on the schema panel.)

Moving through the list of items

You can move through the list of items in the candidate list by pressing:

UP ARROW or DOWN ARROW to move up or down the list one item at a time
If you are at the top of the list, pressing UP ARROW takes you to the bottom of the list; if you are at the bottom of the list, pressing DOWN ARROW 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 to the start or end of the list

Text filtering

You can filter the candidate list by typing the first few characters of a candidate. For example, typing SELECT * FROM curr displays all candidates relevant to SELECT that begin with curr.

By default, SQL Prompt lists all candidates, irrespective of case; if you want to see only those candidates that match the case of your typing, select Enable case-sensitive filtering of candidates on the Listed Candidates, Candidate Types and Filters options page.

If a category does not contain any candidates that match the filter and the current context, it is shown as unavailable. In the example shown above, there are no view or stored procedure names that begin with curr.

Cross-database queries and distributed queries using linked SQL Servers

If you are writing cross-database distributed queries and cross-database queries using different databases on the same linked SQL Server, the candidate list can display the appropriate candidates for you. You can use this feature with databases on SQL Server 7, SQL Server 2000, and SQL Server 2005.

Some examples are shown in Worked Examples.

If you do not want to use this feature, you may wish to clear this check box to improve performance and reduce memory usage.

Selecting candidates for completion

When you have located the required candidate, double-click it or press ENTER to insert it into your query editor.

You can select other completion keys to insert candidates in your query editor on the Pop-up Behavior, Completion Keys options page. For example, you can choose to press TAB, or SPACEBAR, as well as or instead of ENTER.

Using the column picker

The column picker enables you to select multiple columns for tables you have specified in a SELECT statement. The column picker also enables you to select columns for a table you have specified in an INSERT statement.

For an example of how to select columns for the column list in a SELECT statement, see Worked Examples.

Note the following:

The columns are inserted in the order in which you selected them.
By default, the columns are inserted on separate lines.
To insert the columns on a single line, clear the When inserting multiple columns, use a new line for each check box on the Inserted Candidates, Formatting options page.
The columns are qualified if any of the following apply:
- you have selected Qualify column names on the Inserted Candidates, Formatting options page
- you have specified more than one table in the FROM clause
When alias assignment is turned off, columns are qualified with the table name (and the schema (owner) name if Qualify object names is selected). For example:
```
	SELECT [Person].[AddressType].[Name],
	       [Person].[Address].[AddressLine1],
	       [Person].[Address].[AddressLine2],
	       [Person].[Address].[City],
	       [Person].[Address].[PostalCode]
	FROM [Person].[Address]
	INNER JOIN [Person].[AddressType]
	
```
When alias assignment is turned on, columns are qualified with the table alias. For example:
```
	SELECT at.[Name],
	       a.[AddressLine1],
	       a.[AddressLine2],
	       a.[City],
	       a.[PostalCode]
	FROM [Person].[Address] AS a, 
	INNER JOIN [Person].[AddressType] AS at
	
```
SQL Prompt assigns aliases when the table name is inserted.
The columns are inserted according to the formatting you have specified on the Inserted Candidates, Formatting options page; by default, SQL Prompt surrounds identifiers with brackets [ ].

Note that for SELECT * or SELECT table.* statements, you can replace * with all of the columns in the referenced tables, for more information see Worked Examples.

Inserted Candidates

When you select an item from the candidate list, SQL Prompt inserts the candidate according to the options you have set on the Inserted Candidates tab of the Options dialog box. For example, you can specify the case in which you want keywords to be inserted, and you can define options for INSERT statements.

Comments

While you are typing comments, only the Snippets category is available in the candidate list.

Unexpected results

The candidate list may show unexpected results in some circumstances. For example, this may occur when you type SQL statements that contain invalid syntax and SQL Prompt is unable to parse the statement.

For more information, see Troubleshooting.