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.

Displaying the candidate list

You can display the candidate list:

automatically as you type, for example when you type dot (.)
at any time, when you press CTRL+SPACEBAR

To close the candidate list, press ESC.

You can switch off the automatic display or specify how long SQL Prompt will wait before displaying the candidate list by setting the SQL Prompt options.

Using categories

The candidate list displays items grouped by category. You switch between categories by pressing CTRL+LEFT ARROW and CTRL+RIGHT ARROW; you can turn off this feature using the SQL Prompt options. Alternatively, you can select a category by clicking it. Note that if there are no candidates to display for a particular category, you cannot select that category.

Snippets is a list of shortcuts for pre-defined SQL fragments or statements. If an insertion point has been defined, when you select the snippet for insertion, SQL Prompt places the cursor at the relevant position in your query editor.

Column picker shows a list of column names, 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; you select the columns using the check boxes.

Suggested candidates is the default category. The candidates displayed in this category depend on the context of what you type in your query editor. Suggested candidates displays database names , database objects, keywords (displayed in blue or gray), join conditions , data types , built-in functions (displayed in pink), and local variables . By default, the candidates are listed in alphanumeric order; you can apply case-sensitive sort order, if required.

Tables is a list of tables from the current database. If you are using SQL Server 2000, system tables are displayed if you have chosen to display system objects.

Columns shows column name, data type, and the associated table name (or table alias). It also indicates primary key and foreign key columns.

Views is a list of views from the current database and system views if you have chosen to display system objects.

Stored procedures is a list of stored procedures from the current database and system stored procedures if you have chosen to display system objects.

Functions is a list of user-defined functions from the current database and built-in functions.

Other candidates shows keywords, data types, and objects such as users and roles. If you are using SQL Server 2005, this list also shows object types such as synonyms and schemas.

Object types

The Suggested candidates and Other candidates categories can display more than one object type:

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

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

Using the owner list

You can display 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 owner names, click the right arrow button

Note that when you select a candidate, the owner name is inserted in your query editor only if the Qualify object names option is selected.

Resizing the candidate list

You can resize the candidate list by dragging the resize handle in the lower right corner.

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
HOME to take you to the top of the list
END to take you to the bottom of the list

Using text filtering

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

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.

Selecting candidates for completion

When you have located the required candidate, double-click it or press ENTER to insert it in your query editor. Note that you can set other completion keys to insert candidates in your query editor.

Selecting candidates 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.

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.

For example, to select columns for the column list in a SELECT statement:

Type:
SELECT FROM <table_list>

where table_list is a comma-separated list of table or view names.
Place the insertion point after SELECT.
For example:
Press CTRL+SPACEBAR to display the candidate list.
Press CTRL+LEFT ARROW or click to display the Column picker category.
Select the required columns:
- Use UP ARROW and DOWN ARROW to highlight individual columns, and then press SPACEBAR to select the check box.
- Use CTRL+A to highlight all columns, and then press SPACEBAR to select the check boxes.
- Click the check boxes for the required columns; use SHIFT+Click to select consecutive check boxes.
Press ENTER to insert the selected columns in your query editor.

Note the following:

The columns are inserted in the order in which you selected them.
The columns are inserted on separate lines.
You can clear the Use new line for each column option to insert the columns on a single line.
The columns are qualified if any of the following apply:
- you have selected the Qualify column names option
- 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 owner (or schema) name. For example:
```
SELECT [Person].[AddressType].[Name],
       [Person].[Address].[AddressLine1],
       [Person].[Address].[AddressLine2],
       [Person].[Address].[City],
       [Person].[Address].[PostalCode]
FROM [Address], [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 [Address] AS a, [AddressType] AS at
```
Note that SQL Prompt does not assign aliases until you have specified a column or * to select all columns.
The columns are inserted according to the formatting you have specified on the Style tab; by default, SQL Prompt surrounds identifiers with brackets [ ].