Latest version: 5.3
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.
- 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?
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:
- 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.
- Go to the Format > Styles page and save the settings as a style.
- 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
You can navigate easily to the folder where these snippet files are stored:
- From the SQL Prompt 4 menu, select Snippet Manager.
- 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.
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
- 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.
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
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.
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.
Was this article helpful?
Thanks for your feedback!
- SSMS failed to load SQL Prompt add-in 80070002
- No SQL Prompt menu appears in Query Analyzer
- SQL Prompt is showing an old version of my stored procedure (version 3)
- Removing menu items after uninstalling SQL Prompt and SQL Refactor
- A newly-created table exists, but it is not visible in the candidate list (version 3)
- Configuring permissions for SQL Prompt
- Using Intellisense or auto complete with a VS Web project (version 3.5)
- Uninstalling the Visual Studio 2005 add-in
- Unable to cast object of type 'System.DBNull' to type 'System.String' (version 3)
- Suggestions to improve performance for pre-formatted SQL (version 3.9)
- Changing the location of the settings, cache files and snippets folders
- Saving SQL Prompt options (version 3)
- SQL 2008 installation fails with Express Tools rule (version 3.8)
- Add-in failing to load into SQL Server Management Studio on Vista
- Removing SQL Server Management Studio Integration after uninstallation
- Layout SQL Error
- Downgrading to SQL prompt 3.9 from 4.0
- Candidate suggestions being displayed slowly
- SQL Prompt is not showing all linked servers
- Log files
- Refreshing the cache
all SQL products
- Compatibility of Red Gate tools in 64-bit environments
- Application has encountered an error and needs to close
- Error message after installing SQL Toolbelt - The description for Event ID ( 1 ) in Source ( nview_info ) cannot be found.
- Changing the temporary directory used by the installer
- Toolbelt Installer "hanging" while "scanning volumes"
- Login failing with "trusted SQL Server connection" error when using RunAs
- Some Red Gate products identified as containing a trojan by Anti-Virus software
- Activation may fail with Unknown Error -1
- Product uses web help although a CHM file is available locally
- Argument exception resulting from missing environment variable
- Check for updates may fail when used through proxies
- 'Unidentified Publisher' error when repairing or uninstalling
- Licensing activates product as standard edition
- Moving Red Gate software products to another machine
- Red Gate tools log locations
- The application UI opening slowly when there is no internet access
- Activating your products
- Activating your products
- Getting help offline
- Customizing SQL Prompt to write SQL code more efficiently
- SQL Prompt 3.xx - reducing memory usage to improve performance
- Using SQL Prompt 4: tips for SQL Prompt 3 users
- SQL Prompt release notes - version 4.xx
- Release notes - version 5.xx
all SQL products
- Red Gate product acknowledgements
- Activating your products
- Activating your products
- Red Gate bundle history
- Check for updates
- Troubleshooting Check for Updates errors
- Current versions
- Deactivating your products
- Installing Red Gate products from the .msi file
- Requesting additional activations
- Serial numbers for bundles
- Reactivating using a different serial number
- Extending your trial
- Finding your serial numbers
- Moving a serial number from one computer to another
- No response received for manual activation
- Licensing and activation resources
- Licensing and activation resources
- Troubleshooting licensing and activation errors
- Licensing and activation FAQs
- Red Gate tools log file locations
- Download old versions of products
- Download product prerequisites & utilities
- Support & upgrades
- Upgrading your software
- Upgrading FAQs