Notes & articles

Using SQL Prompt 4: tips for SQL Prompt 3 users

If you are already familiar with the features of SQL Prompt 3, you may be wondering how to perform the same tasks or find an equivalent feature in SQL Prompt 4.

SQL Prompt 4 introduces several changes to the user interface, and certain features from SQL Prompt 3 have been moved, renamed, or are no longer available in SQL Prompt 4.

This article answers common questions that you may have as an experienced SQL Prompt 3 user.

Key differences

• Terminology changes
• Options that are new at SQL Prompt 4
• SQL Prompt 3 options that are no longer available
• Working offline

Frequently asked questions

• How can I import my snippets from version 3 to version 4?
• How can I import my aliases from version 3 to version 4?
• Using my version 3 layout settings in version 4
• When is the suggestions box triggered?
• How do I edit options for SQL Prompt?
• How can I quickly insert columns into a table?
• How do I change the keys that insert suggested code?
• How do I control the behavior of the suggestions box?
• How do I lay out my code using SQL Prompt?
• How can I save my preferences for formatting code?
• How do I manage my snippets?

Terminology changes

The candidate list is called the suggestions box in SQL Prompt 4.

Suggestions box in SQL Prompt 4:

The schema panel is called the object definition box in SQL Prompt 4.

Object definition box in SQL Prompt 4:

The Layout options for making your SQL code more readable are now available in the Format pages of the Options dialog box:

Options that are new at SQL Prompt 4.0

The following options are new for SQL Prompt 4:

Save and import/export format settings

When you have set up your format settings, so that your SQL queries are laid out how you want them, you can save these settings:

1. In the SQL Prompt Options dialog box, use the Format pages to lay out your queries.

   You can preview the effect of each option using a sample query, or the current query in the editor window.

2. Go to the Format > Styles page and save the settings as a style.

   

3. To save the format settings as a file that you can share with other SQL Prompt users, click Export.

For more information, see Using styles to format your code.

Locate individual snippet files on disk

In SQl Prompt 4, each snippet is stored as a separate .sqlpromptsnippet file.

You can navigate easily to the folder where these snippet files are stored:

1. From the SQL Prompt 4 menu, select Snippet Manager.
2. Click Locate on Disk.

If you want to import snippet files from other users, close the Snippet Manager dialog box, then copy the snippet files to this folder. The next time you display the Snippet Manager, the snippets will appear in the list.

Decrypt encrypted objects

SQL Prompt 4 can display the creation script for encrypted objects in the object definition box. On the Suggestions > Types of suggestion page, turn on Decrypt encrypted objects.

Note: This option is available in Pro Edition only.

Indent subclauses

This is a new formatting option available at SQL Prompt 4. On the Format > Data statements page of the Options dialog box, turn on Indent subclauses to indent all sub clauses inside SELECT, UPDATE, INSERT and DELETE statements.

You can preview the effect of turning on this option (and other formatting options) in the Preview pane.

Context menus for creating snippets and formatting code

You can right-click in your query editor, and apply the following SQL Prompt commands:

• Create Snippet
• Format SQL

Notes

• If none of your code is selected, Format SQL applies to the entire query; otherwise, it is applied only to the highlighted selection.
• When you create a snippet from a highlighted selection of code, SQL Prompt automatically names the snippet using the first letter of each word in the selection. You can rename the snippet before saving by typing the name in the Snippet box. For more information, see Managing snippets.

Options that are no longer available

Many options from SQL Prompt 3 have been removed from SQL Prompt 4, to make the interface clearer and the program simpler to use. The following SQL Prompt 3 options are not available in SQL Prompt 4:

Specify prompting only / layout only

The Use SQL Prompt option in SQL Prompt 4 applies to both prompting and layout features.

To use only the layout options, in the SQL Prompt Options dialog box clear the Automatically trigger suggestions option. The suggestions box will not be displayed from now on, unless you choose to manually trigger it, but your code will continue to be formatted by SQL Prompt.

You can also turn off the display of object definitions and tooltips under Tooltips, on the same page of the Options dialog box.

Define custom trigger words

SQL Prompt 4 always offer suggestions where appropriate; you do not need to define a list of trigger words.

Enable category navigation keyboard shortcuts

Using the keyboard to move around in the suggestion box is always enabled in SQL Prompt 4. For a list of keyboard shortcuts, see <HYPERLINK>

Specify height and width of candidate list in pixels

You can change the width and height of the suggestions box in SQL Prompt 4 using the drag handle at the bottom right of the box:

Remember candidate list size between sessions

The size of the suggestions box is always remembered between sessions.

Candidate list font options

The font used for suggestions in the suggestions box in SQL Prompt 4 cannot be customized.

Turn off prompting for variables, scripted objects and parameters

SQL Prompt 4 always prompts for variables, scripted objects, and parameters where appropriate.

Display exact matches at the top of the candidate list

In SQL Prompt 4, exact matches are always displayed at the top of the suggestions box.

Suggestions are always grouped by type (for example, all tables are listed together), and sorted alphabetically within each group.

Insert parentheses for built-in functions and data types

SQL Prompt 4 automatically inserts parentheses for built-in functions and for relevant data types.

Use a new line when inserting multiple columns

In SQL Prompt 4, you can control in more detail how columns are inserted on the Format > Data statements page of the Options dialog box.

Insert new line at current indentation level

In SQL Prompt 4, indentation of inserted lines is controlled by the options on the Format > Data statements page of the Options dialog box.

Force EXECUTE statements onto a single line

In SQL Prompt 3, this option was available in the Inserted Candidates / SQL page.

In SQL Prompt 4, this option has been replaced with For execute statements, place each parameter on a new line on the Format > Expressions page of the Options dialog box.

Snippet categories

Snippets cannot be categorised in SQL Prompt 4.

Insert snippets at current indentation level

Snippets are always inserted at the current indentation level in SQL Prompt 4.

Insert snippets without using the candidate list

In SQL Prompt 4, when you start typing a snippet name, it will automatically appear in the suggestions box (except in certain circumstances when to insert the snippet would be invalid, for example when you are entering a name inside quotation marks). Press Tab or Enter to insert the snippet code.

If you paste in some code into your query editor that contains snippets, move the cursor to after the snippet name, to view a tooltip:

You can then press TAB to insert the snippet.

Enable TAB to insert columns after SELECT *

In SQL Prompt 4, pressing TAB will always insert columns after typing SELECT * (when there is a table or view name specified in the rest of the statement)

Behavior options for inserting closing characters

These options were available in Auto Insert / Closing Characters in SQL Prompt 3.

SQL Prompt 4 now has sensible defaults for this type of behavior:

• Typing a closing character, such as ) or ] will automatically be ignored if the closing character has already been automatically inserted by SQL Prompt.
• Deleting a closing character does not automatically delete the opening character.
• Insert closing character only white space is present after caret - SQL Prompt 4 now automatically checks for white space; if there is no white space after the caret, it does not insert the closing character.

Enable cross-database and linked server support

Cross-database queries are always enabled in SQL Prompt 4. Linked server support is still an option at SQL Prompt 4:

On the Suggestions > Types of suggestion page in the Options dialog box, turn on Enable linked server support.

Search a fixed number of lines from caret / Search entire batch/GO block

You can no longer specify the number of lines above and below the current position of the caret that SQL Prompt will search for variables and parameters.

When editing large scripts or pasting in large chunks of code, SQL Prompt 4 now automatically searches a fixed number of characters before and after the current position of the caret.

Search for objects and aliases when opening files or pasting text

SQL Prompt 4 will automatically search for objects and aliases in these circumstances, up to a fixed number of characters.

Insert single match without displaying the candidate list

This option is no longer relevant to SQL Prompt 4. The suggestions box is always displayed - when therre is only one match, it will be the only suggestion listed in the suggestions box; press TAB or ENTER or any defined insertion key to insert it.

Layout schema statements/data statements/expressions

These options were available in the Layout pages of the SQL Prompt 3 Options dialog box.

In SQL Prompt 4, you set the specific format options you want on the following pages:

• Schema statements
• Data statements
• Expressions

Cache management dialog box

The cache management dialog box is no longer available in SQL Prompt 4. Only objects from databases to which you have connected during your current session are cached in SQL Prompt 4. You can refresh the current database schema by doing the following:

From the SQL Prompt 4 menu, select Refresh Suggestions; or press Ctrl+Shift+D.

Working offline

SQL Prompt 4 does not cache database objects on disk when you are not connected to a SQL Server instance. When you close down your query editor, the SQL Prompt memory is cleared of all objects.

This means that suggestions for database objects are not available when you are not connected to your database.

For more information about connecting to databases and use of memory, see Managing connections and memory.

Importing version 3 snippets into version 4

If you have created your own snippets in version 3 of SQL Prompt, you can continue to use them in SQL Prompt 4; to do this you need to import the snippets using a utility provided by Red Gate.

For information on how to import snippets, see http://www.red-gate.com/MessageBoard/viewtopic.php?t=9361

Importing version 3 aliases into version 4

If you have created your own custom aliases in version 3 of SQL Prompt, you can continue to use them in SQL Prompt 4; to do this you need to import the aliases using a utility provided by Red Gate.

For information on how to import aliases, see http://www.red-gate.com/MessageBoard/viewtopic.php?t=9362

Using version 3 layout settings in version 4

You cannot directly import your layout preferences from SQL Prompt 3 into 4.0, as the format in which the layout options are stored is not compatible between the two versions.

To use the same layout settings in version 4.0, you will need to set them up again using the Format pages of the Options dialog box. Once you have configured the options the way you want, you should save them as a style. This will allow you to quickly reapply your layout settings whenever you want, or share them with other users.

Fore more information, see Using styles to format your code.

When is the suggestions box triggered?

In version 3 of SQL Prompt, the candidate list was triggered when you typed any of the defined trigger words.

In SQL Prompt 4, there is no list of specific trigger words. The suggestions box is automatically triggered in the following circumstances:

• As soon as you type any character or press the SPACE bar. (The suggestions are filtered as you type more characters).
• When you click inside parenthesis.

The suggestions box is not automatically triggered when:

• you may be typing an alias
• you are declaring a variable name, or table name etc.
• you are typing inside a string

To close the suggestions box, press the ESC key, or click anywhere outside of the box.

How can I quickly insert columns into a table?

In SQL Prompt 4, the Column Picker is available as a separate tab in the suggestions box, below the list of suggestions.

When the suggestions box is displayed, press Ctrl + LEFT or click the Column Picker tab to switch to the column picker.

Note: The Column Picker is unavailable when it would invalidate the syntax of your query to insert multiple columns into your query.

By default columns are listed alphabetically. To view them in the order they are scriped in the table, select List in table order.

Columns are inserted in the order in which you select them.

To select columns:

• use the UP and DOWN arrows to move through the list
• press SPACE to select a column for insertion

For more information, see Inserting columns with the column picker.

How do I edit options for SQL Prompt?

When you install SQL Prompt, a SQL Prompt 4 menu is added to your query editing application:

On the SQL Prompt 4 menu, select Options to display the SQL Prompt Options dialog box:

This dialog box has been redesigned from version 3.0; the options are now grouped into categories on the left. Click a page (for example Inserted code > Objects & statements) to review or modify options relating to that feature.

Note: If you are using the standard edition of SQL Prompt, rosettes indicate that some of the Format options pages are not available. Format options are only available in SQL Professional edition.

Snippets are now created and edited in a separate Snippet Manager dialog box. See Managing snippets.

How do I change the keys that insert suggested code?

On the SQL Prompt menu, select Options to display the SQL Prompt Options dialog box.

In the SQL Prompt Options dialog box, go to the Main > Behavior page, then select the keys you want under Insertion keys:

How do I control the behavior of the suggestions box?

By default, the suggestions box is displayed automatically whenever you start typing in your query editor, as in SQL Prompt 3. As you type, the list of matching suggestions is filtered based on the string of typed characters.

You can adjust how the suggestions box behaves in a number of ways. In SQL Prompt 4, these options are available on the Main>Behavior page of the Options dialog box.

Do not automatically display the suggestions box

Clear the Automatically trigger suggestions check box to specify that SQL Prompt should only display the suggestions box when you manually trigger it.

From now on, press Ctrl + Enter to display the suggestions box .

Change how long it takes for the suggestions box to display

Enter the value in milliseconds in the box below Automatically trigger suggestions.

Note: this delay does not apply when you manually trigger the suggestions box.

Adjust the size of the suggestions box

You can drag the bottom right corner of the suggestions box to increase or decrease its size.

The size is remembered between editing sessions.

How do I lay out my code using SQL Prompt?

To format your queries, use the Format pages in the SQL Prompt Options dialog box:

• Case - sets upper or lower case for keywords, functions and data types.
• Schema statements - controls the layout of CREATE and ALTER statements
• Data statements - controls the layout of SELECT, INSERT, UPDATE and DELETE statements
• Expressions - controls spaces, indents and new lines for expressions and parts of statements
• Commas & parentheses - controls position of commas in statements
• Tabs & wrapping - settings for number of tab spaces and when to wrap lines

Click the Current Query tab to preview the effect of any change on the SQL you are currently editing:

The preview shows the first few lines of your query; if you have a block of code selected in your query, this will be used as the preview.

If you don't have a query currently open, then only the Sample SQL pane is available. This shows the effect of your format options on an example chunk of code.

Note: In SQL Prompt 4, you can save all your formatting settings as a style. See the next section.

How can I save my preferences for formatting code?

SQL Prompt 4 allows you to save all your formatting preferences to reapply them to a query at a later date, or share them with other SQL Prompt users so they can use the same conventions for formatting their queries.

You can create, edit, import and export formatting styles on the Format > Styles page of the SQL Prompt Options dialog box:

When you save a new style, it saves the current setting of each option in all the Format pages. To check that this is the layout you want, use the Current Query tab to preview the first few lines of code in your current editor window.

For more information on styles, see Using styles to format your code

How do I manage my snippets?

In SQL Prompt 4, snippets are managed in their own new dialog box:

From the SQL Prompt 4 menu, select Snippet Manager.

You can create, edit, and delete snippets in the Snippet Manager in the same way as in version 3.

Note: snippet categories are no longer available in SQL Prompt 4.