Getting started

SQL Prompt provides code auto-completion for SQL Server Management Studio, SQL Server Management Studio Express, Query Analyzer, and Visual Studio®, enabling you to write accurate SQL scripts much more quickly.

SQL Prompt displays a suggestions box as you type, or when you press CTRL+SPACEBAR. The suggestions box shows items based on what you have typed.

SQL Prompt is compatible with SQL Server 2008, SQL Server 2005, and SQL Server 2000. SQL Prompt supports keywords and syntax from SQL Server 2008, 2005 and 2000.

You can use SQL Prompt to:

  • insert suggested keywords and object names into your code
  • expand column lists in SELECT * and SELECT table.* statements by pressing TAB
  • view and select columns quickly using the column picker
  • format SQL code according to a range of options (Pro Edition only ProRosette)
  • view schema information to see the definition of an object
  • qualify object names and column names
  • execute stored procedures
  • create snippets of code for queries you run regularly
  • assign aliases automatically, including custom aliases
  • customize auto-completion behaviour by specifying options

SQL Prompt supports cross-database queries as well as distributed queries with linked SQL Server instances.

Getting more information

  • What's new in this version of SQL Prompt?
  • Installing and running SQL Prompt
  • Find out about activating SQL Prompt.
  • How can I check for updates to SQL Prompt?
  • Find out more about Red Gate.

© Red Gate Software Ltd. All Rights Reserved.


Installing and running SQL Prompt

To install SQL Prompt, you must have administrator privileges. Close any open queries before you start.

If you are upgrading from a previous version, the installer does all the preparation for you.

Running SQL Prompt

When you install SQL Prompt, add-ins to the following editors are automatically installed:

  • SQL Server Management Studio
  • SQL Server Management Studio Express Edition
  • Query Analyzer
  • Microsoft Visual Studio 2005 and 2008

Start any of these editors to begin using SQL Prompt. SQL Prompt features are available from the SQL Prompt 4 menu:

Customizing SQL Prompt

You can customize how SQL Prompt suggests, inserts and formats your code, to more closely match the way you prefer to write queries. To change any of the default settings in SQL Prompt:

On the SQL Prompt 4 menu, select Options.

See Setting main behavior options and Options for formatting your code.

Activating SQL Prompt

When your trial period expires, you need to enter your serial number to activate the product:

From the SQL Prompt 4 menu, select Serial Number > Enter Serial Number.

Find out more about activation

Uninstalling SQL Prompt

To uninstall SQL Prompt:

  1. Use the Windows® Add or Remove Programs tool to remove SQL Prompt 4.
  2. In SQL Server Management Studio, to remove the SQL Prompt 4 menu from the toolbar, do the following:
    • On the Tools menu, click Customize.
    • Drag the SQL Prompt menu from the menu bar, or right-click the SQL Prompt 4 menu and click Delete.
  3. Close the Customize dialog box.

Notes on using SQL Prompt with Visual Studio

SQL Prompt can be integrated with Visual Studio 2005 and Visual Studio 2008. SQL Prompt only works in Database projects and SQL Server projects.

Notes on using SQL Prompt with Query Analyzer

By default, SQL Prompt starts automatically whenever you start Query Analyzer. To switch off support for SQL Prompt in Query Analyzer, right-click the SQL Prompt 4 icon PromptTrayLogo in the desktop notification area and click Start SQL Prompt when you start Query Analyzer so that it is not ticked.

To remove the SQL Prompt 4 icon PromptTrayLogo from the notification area, right-click the icon and click Exit.

To add the SQL Prompt 4 icon PromptTrayLogo so that you can switch on or switch off support for SQL Prompt with Query Analyzer, click the Windows Start menu, and on the Red Gate > SQL Prompt 4 menu, click SQL Prompt Query Analyzer Integration.


Using SQL Prompt: examples

The following examples show how SQL Prompt can help you to write queries more quickly. All these examples use the Adventure Works database for SQL Server 2005.

Example 1: Writing a simple query

  1. In your query editor, start a new query on the AdventureWorks database.
  2. Type:

    se (press TAB) * fr

    As you type, the suggestions box is displayed. Whenever you press the TAB button, SQL Prompt inserts the currently highlighted suggestion into the query:

    Press TAB, then a space, then type:

    p

    The suggestions box shows all relevant suggestions beginning with p :

    Note: SQL Prompt always places the most contextually relevant types of suggestions at the top of the list, depending on what you have typed in your query.

  3. Press the down arrow once to highlight the ProductCategory table. You can check if this is the correct table by looking at the column names and data types in the object definition box:

  4. Press ENTER to insert the table and complete the query. Your query should now look like this:

Note that in SQL Server 2005 and 2008, SQL Prompt automatically inserts the schema name for objects in non-default schemas.

Quick tips:

  • Use the UP and DOWN arrow keys to move through the list of suggestions.
  • TAB or ENTER inserts the current suggestion. To set other insertion keys, for example SPACE or DOT (.), see Setting main behavior options.
  • To view the object creation script for an object instead of a summary, click the Script tab in the object definition box. The Script tab will be the default view from this point onwards.
  • To turn off the automatic display of the object definition box, see Using the object definition box.
  • Note that SQL Prompt automatically replaces lower case keywords with upper case as you type. You can change the case used for keywords. See Options for formatting your code.
  • You can also change how object names are qualified when inserted into your code. See Inserting suggestions into your code.

Example 2: Using the Column Picker to select and insert columns

In this example, SPACE is used as an additional insertion key. To define SPACE as an insertion key:

  • From the SQL Prompt menu, select Options, then
  • In the Options dialog (Main > Behavior page), under Insertion Keys, select Space bar, then click OK.

From now on, each time you press SPACE, the currently highlighted suggestion will be inserted into your query.

  1. Create a new query and type:

    se * fr cus (include all the spaces)

    then press ENTER.

    SQL Prompt will insert the matching suggestions each type you press SPACE, so your query should now look like this:

    Note: you can click just after the * and press TAB to insert all columns:

  2. To insert specific columns from the CUSTOMER table, rather than all the columns, move the cursor to just after the * and click the BACK button to delete *.
  3. Press CTRL+SPACE to display the suggestions box.
  4. Press CTRL+LEFT to show the Column Picker:

    The column picker allows you to choose multiple columns from the table. Primary key [image] and foreign key [image] columns are indicated by key icons.

  5. Use the up and down arrows to move throught the list, then press SPACE to select a column for insertion. You can also use the mouse to click the check box for each column to be inserted:

  6. Press ENTER to insert all the columns into your query. Your query should now look like this:

    Columns are inserted in the order in which they were selected; and automatically formatted based on the options set in the Format pages of the SQL Prompt Options dialog.

Quick tips:

  • By default, columns are listed in alphabetical order. To list them in the order they are defined in the table, select List in table order. When inserting columns from more than one table, List in table order groups columns by table rather than in a single alphabetical list.
  • When the Column Picker is displayed, type the first few letters of a column name to filter the list. Any columns already selected are displayed at the bottom of the list and will still be inserted.
  • Clicking Select All toggles between selecting all columns and selecting none.
  • You can change how SQL Prompt lays out the inserted code (for example, to place each column on a new line). See Formatting your code.
  • The first time you display the column picker, a helpful tooltip appears; click X to close it. Once you close it, the tooltip will not reappear.

Example 3: Using a snippet to insert a block of code

  1. Create a new query and type s

    The suggestions box shows all suggestions that start with s.

  2. Press CTRL+DOWN twice to change the category of suggestions to Snippets.
  3. Press DOWN to move down the list of snippets to highlight ssf.

  4. Press any of the insertion keys (for example SPACE, TAB or ENTER) to insert the snippet code. The position of the cursor after the snippet has been inserted is specified as part of the snippet definition.

Quick tips:

  • Use Ctrl+DOWN to change the category of suggestion (for example, to see only views or stored procedures in the suggestions box).
  • To make a snippet from a block of code in your editor, highlight it, then right-click and select Make Snippet.
  • SQL Prompt is pre-configured with a large number of default snippets. To see a list of all snippets, and to edit, delete or create new snippets, from the SQL Prompt menu, select Snippet Manager.

For more information on snippets, see Managing snippets.

Example 4: Writing a full INSERT statement

  1. Create a new query and type ii.
  2. This is a snippet; Press TAB to expand it to an INSERT INTO statement and SPACE to display the suggestions box again:

  3. Type cus to filter the list to the two customer tables, then press ENTER to insert the first (highlighted) table, customer.

    SQL Prompt completes the syntax of the INSERT statement, and inserts the name, data type and default value for each column as comments:

    The insertion point is positioned for you to insert the values.

Quick tips:

  • By default, column names, data types and default values are automatically inserted when you write an INSERT statement. You can turn off some or all of these defaults using the SQL Prompt options dialog; see Customizing inserted code.
  • To see a list of columns in a table, and their data type, move the mouse pointer over a table name to display the tooltip

    then click the tooltip:

Example 5: Executing functions and stored procedures

  1. Create a new query and type EXEC
  2. Press SPACE.

    The suggestions box is displayed, with all the functions and stored procedures at the top of the list. Select a function in the list, then select the Script tab to see a preview of the creation SQL in the object definition box for the selected object.

    Note: You can click the Summary tab in the object definition box to view a list of parameters for that function:

  3. Press ENTER to insert the function into your code:

Quick tips:

  • By default, parameters are automatically inserted into functions and stored procedures, and the data type and default value is displayed for each paramater. You can change this behavior in the SQL Prompt Options dialog box. See Customizing inserted code.
  • To see only stored procedures in the suggestions box, click the All Suggestions tab at the bottom of the suggestions box, and then click Stored Procedures:

  • To view system functions and stored procedures in the suggestions box, select List system objects on the Suggestions > Types of suggestion page in the SQL Prompt Options dialog box.

Example 6: Writing a cross-database query

Cross-database queries are automatically supported in SQL Prompt 4. You do not need to select an option to enable them.

  1. Create a new query on the AdventureWorks database.
  2. Type SELECT * FROM pub.
  3. SQL Prompt lists the pubs database as a matching suggestion:

  4. Press TAB to insert the database name into your query, then type . (DOT). As soon as you press DOT, SQL Prompt reads all the database objects from the pubs database. (For large databases, this may take a few minutes).
  5. If you do not have permissions to connect to that database, or to access some objects in the database, a warning dialog is displayed:

    Note: This warning may also be displayed if SQL Prompt has run out of memory to store the objects from this database. See Managing connections and memory.


Inserting suggestions

In this chapter

  • Inserting suggestions into your code
  • Inserting columns with the column picker
  • Using the object definition box and object tooltips
  • Working with aliases


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.

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

Suggestions are listed in a sensible order, depending 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.

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

  1. From the SQL Prompt 4 menu, click Options.
  2. Go to the Suggestions > Types of suggestion page.
  3. Select List all database columns after a SELECT statement.

All column names will now be displayed in the suggestions box when you type SELECT, listed alphabetically:

Only displaying the suggestions box manually

To turn off the automatic display of the suggestions box:

  1. From the SQL Prompt 4 menu, select Options.
  2. On the Main > Behaviour page (displayed by default when you first display the SQL Prompt Options dialog box), clear Automatically trigger suggestions.

When automatic display of the suggestions box is turned off, you can press CTRL+SPACE 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:

Only the categories that contain suggestions are available to select.

To switch between categories, click the All Suggestions drop-down or press CTRL+UP or CTRL+DOWN.

The following categories are shown:

IconTables | Tables lists tables from the current database.
IconViews | Views lists views from the current database
IconColumns | Columns lists column names, with their data types and associated table names (or table aliases). Primary key [image] and foreign key [image] 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.
IconSprocs | 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
systemvariable | System variables | ButtonJOINSUGGESTION | Join suggestions
Linkedserverobject | 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 Rightarrow in the bottomleft corner of the suggestions box.

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

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

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 [image] . 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, Query Analyzer, or Visual Studio.

Moving through the list of suggestions

You can move through the list of items in the suggestions box 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 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:

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.

Expanding SELECT * to insert all columns

When you use a * wildcard in your query to reprent all the columns in a table, you can expand this to explicitly list all the columns in the table.

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.

Encrypted objects

SQL Prompt can automatically decrypt encrypted objects to show their creation script in the object definition box. (Available in SQL Prompt 4 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:

  1. From the SQL Prompt 4 menu, select Options
  2. Go to the Suggestions > Types of suggestion page.
  3. Turn off Decrypt encrypted objects


Inserting columns with the column picker

The Column Picker enables you to select multiple columns for tables or views specified in a SELECT statement or similar syntax.

When the suggestions box is displayed, press CTRL + left arrow or click on the Column Picker tab to display the Column Picker:

Primary key [image] and foreign key [image] columns are indicated by key icons.

The Column Picker is never automatically selected.

  • The columns are listed in the order in which they are defined in the table. Clear the List in table order checkbox to list them alphabetically.
  • The first column in the list is highlighted, but it is not selected. Only columns that you specifically check will be inserted.

Note: the Column Picker is only available when it is valid syntax to insert more than one column into your code at this point in the query, and there are columns available to insert. For example, the column picker is available for SELECT statements and after ORDER BY, but not in a WHERE clause.

Selecting columns

As you start typing, the list of columns is filtered to show only those that match the typed characters.

  • Use the UP and DOWN keys to navigate through the list.
  • Press the SPACEBAR to select a column to be inserted.
  • You can select all the colums in the list by pressing CTRL+A or clicking the Select All button.

If you have already selected some columns and then filter the list by typing some letters, the previously selected columns are listed at the bottom:

Inserting selected columns

Press the Return or TAB key to insert the selected columns. The columns are inserted in the order in which you selected them.

Note: You can also insert the selected columns using any of the other insertion keys you have specified, for example Space Bar or Dot(.) or Comma(,) - see Setting main behavior options.

The inserted columns are automatically formatted depending on the options specified in the SQL Prompt Options dialog box:

  • for SQL Prompt Pro Edition, under New lines on the Format > Data Statements page.
  • for SQL Prompt Standard Edition, under Column Placement on the Format > Basic (Standard Edition) page

If the format of the inserted columns doesn't look the way you want, you can change the formatting options and then apply the new format to the selected code:

From the SQL Prompt 4 menu, select Format SQL (this command is only available in SQL Prompt Pro Edition).

Qualified column names

Inserted column names are qualified when:

  • you have selected Qualify column names with table name on the Inserted code > Qualification options page, or
  • you have specified more than one table in the FROM clause, and the column names would be ambiguous without also specifying their table

Both qualification options turned on:

Column names automatically qualified to avoid ambiguity:

Note: in the above example, only those columns that appear in more than one table are qualified.

When Assign aliases is turned on as well as Qualify column names with table name, columns are qualified with the table alias. For example:

You can customize how aliases are created and inserted. For more information, see Working with aliases.


Using the object definition box and object tooltips

SQL Prompt displays object definitions in an object definition box. For example, when you select a table in the suggestions box, the object definition box pops up to show the creation SQL for that table, and a summary of the object.

  • For tables and views, the Summary tab shows the column names and data types.
  • For stored procedures and functions, the Summary tab displays parameters and their data types, and for functions the data type that is returned.
  • For snippets, the snippet code is shown on the Script tab. The Summary tab is not available for snippets.
  • For temporary tables, CTEs and table valued variables, only the Summary tab is available.

To resize the object definition box, drag the resize handle [image] at the bottom left or right corner.

Showing or hiding the object definition box

When you first install SQL Prompt, the object definition box will always be displayed for any suitable object selected in the suggestions box. To turn it off, so that it isn't displayed automatically:

  1. On the SQL Prompt menu, select Options.
  2. On the Main > Behaviour page of the SQL Prompt Options dialog box (this page is the first page displayed by default), turn off Display object definitions.

Note: If you turn off the object definition box, you can still view the object creation script for any object in your script by hovering over the object name to display a tooltip. Click the tooltip to display the object definition.

You can also increase the time lag before the object definition box is displayed. This may be useful if you don't want it to appear when you are typing code quickly, but you do want to see definitions for certain tables after a slight pause:

Enter the time in milliseconds. 500 milliseconds is the default time.

Copying object definitions

You can copy the information in the object definition box to the clipboard:

  • To copy all the information, ensure that there is no text selected, and click Copy.
  • To copy part of the information, select the required text, and click Copy.

Using object definition tooltips

When you move the mouse pointer over an object in your query, SQL Prompt displays a tooltip containing the fully qualified object name:

The tooltip also displays the MS_Description extended property for the object, in this case a brief explanation of the table's contents.

If the object name is underlined, you can click it to display the object definition box for the object:

Note: for functions, the tooltip displays the possible parameters for that function.

Using tooltips for more help on system functions and parameters

Move the mouse pointer over a system (built-in) function to display an interactive tooltip. You can click on the tooltip to access the SQL Server online help for that function:

You can also the mouse pointer over the function parameters to display a tooltip specifically listing the valid parameters for this function:

Note: As you enter the parameters, the next expected parameter is highlighted in blue.

Click either type of tooltip to read more about the current function in Books Online:

Showing or hiding tooltips

When you first install SQL Prompt, tooltips are enabled by default. To turn them off:

  1. On the SQL Prompt menu, select Options.
  2. On the Main > Behaviour page of the SQL Prompt Options dialog box (this page is the first page displayed by default), turn off Objects or Parameters under Tooltips.

Tooltips are displayed for these objects:

  • tables
  • views
  • stored procedures
  • databases
  • data types
  • snippets
  • triggers
  • local variables (type)
  • local parameters (type)
  • columns (table name and type)
  • built-in functions (function signature)
  • user-defined functions (function signature)

Viewing tooltips for aliases

SQL Prompt displays a tooltip when you have created an alias for a table or view. Aliases are identified by the AliasIcon icon in the tooltip:

You can click the tooltip to view the summary of columns in that table or view.

Note: aliases for views are also identified as "Table" in the object tooltip.


Working with aliases

You can set SQL Prompt to automatically assign an alias to each table and view that is referenced in a SQL statement. You can also define custom aliases for tables and views.

To manage aliases:

  1. On the SQL Prompt menu, click Options.
  2. In the SQL Prompt Options dialog box, select the Inserted code > Aliases page.

Alias assignment

When the Assign aliases check box is selected, SQL Prompt assigns an alias to tables and views that are referenced in a SQL statement, provided that you have specified a list of columns or used * to select all columns.

For example, if you select all columns from the table Contact, SQL Prompt creates the alias c to represent the table name Contact.

If you do not want SQL Prompt to include the AS keyword when it assigns aliases, clear the Include AS in alias definition check box.

Where possible, SQL Prompt generates aliases using the first letter of the table or view name. SQL Prompt also takes into account:

  • underscores

    TBL_Contact is assigned the alias tc

  • hyphens

    hyphenated-tablename is assigned the alias ht

  • case

    MixedCase is assigned the alias mc

The suggestion box displays the learned aliases at appropriate points in your query, for example when you are typing a WHERE clause or adding additional columns to your query.

SQL Prompt creates additional aliases whenever there is ambiguity, for example in self-joins:

Custom aliases

If the aliases that SQL Prompt automatically generates do not satisfy your naming conventions, you can specify user-defined aliases for table or view names.

To add a user-defined alias:

  1. Under Custom aliases, click New.
  2. In the Alias Define Custom Alias dialog box, type the name of the table or view in the Object name box and the alias in the Alias box.
  3. Click Save.

For example, if you specify the user-defined alias Con for the Contact table:

... then SQL Prompt assigns the alias as follows:

To delete a custom alias, select the alias that you want to delete, and click Delete.

Prefixes to ignore

You can specify that SQL Prompt should ignore a prefix when assigning an alias for a column, table, or view name.

To add a prefix to ignore when generating an alias:

  1. Under Prefixes to ignore, click New.
  2. In the Prefix to Ignore dialog box, type the name of the prefix.
  3. Click Save.

For example, if you specify TBL as a prefix to ignore and you have a table called TBL_Orders, SQL Prompt considers only Orders when assigning an alias for the table name:

Notes

  • Prefixes are not case-sensitive. Specifying tbl, TBL, or Tbl as the prefix will result in the SQL Prompt behavior shown above.
  • You do not need to include the underscore in the prefix name.


Formatting your code

In this chapter

  • Formatting your code
  • Using styles to format your code
  • Importing and exporting styles


Formatting your code

Formatting your code using SQL Prompt is only available in SQL Prompt Pro edition.

Setting the format options

To set formatting options, from the SQL Prompt menu, select Options. In the SQL Prompt Options dialog box, formatting options are all available in the Format group:

Click on the page containing the type of format options you want to set.

Note: The settings you choose will only affect the appearance of your code when you click OK to save those settings and close the SQL Prompt Options dialog box and then insert suggestions or select Format SQL in your query editor window.

For more information about the formatting options, see Options for formatting your code.

Previewing the format options

There are two ways to preview the effect of applying the current format options to your code:

  • The Sample SQL view shows how an example query would be formatted using the current options.
  • The Current Query view shows how the first few lines of the query that is currently active in the editor window of your SQL editing application (eg SSMS) would be laid out using the current options.

Note: Current Query is not available when you are not currently editing a query.

Formatting your SQL

To format the SQL code in the current query editor or highlighted SQL code fragment:

  • On the SQL Prompt menu, click Format SQL, or
  • Press CTRL+K, Y (In Query Analyzer, press CTRL+SHIFT+Y.)

Note: not all SQL commands can be refactored by SQL Prompt. For unsupported commands only the wrapping option is applied.

Saving your format options as a style

When you have set up all your format options, you can save them as a style. Styles allow you to reapply all your format options without having to change each one again.

See also

Importing and exporting styles


Using styles to format your code

A style contains all your preferred settings for formatting SQL code. Saving your formatting options as a style allows you 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.

Overview: applying a style to your code

  1. In the Format > Styles page of the SQL Prompt Options dialog box, select the style you want to use from the Styles drop-down list.

    Before you apply the style, you can preview how it will lay out the first few lines of your current query - select the Current Query preview tab. The style is not applied to your code until you specifically choose to apply it in your code editor.

  2. Click OK to close the SQL Prompt Options dialog box.
  3. Select the code you want to format in your SQL editor, right-click and click Format SQL.

    If you don't select any code, the entire query will be formatted.

Note: SQL Prompt cannot format your query if it contains syntax errors. A message will be displayed to indicate when there are syntax errors.

Choosing a style

From the SQL Prompt Options dialog box, select Format > Styles.

Select a style you have previously created from the Style drop-down list.

An asterisk * next to a style name indicates that some format options currently selected in SQL Prompt do not match those previously saved as this style. If you close the SQL Prompt Options dialog box and then format the code, it will use the current settings rather than the settings saved in the style.

If the style you want to use has been emailed to you, or is available on your network, you will first need to import it into SQL Prompt. See Importing and exporting styles.

Previewing the effect of the style

There are two ways to preview the effect of applying the current format options to your code:

  • The Sample SQL view shows how an example query would be formatted using the current options.
  • The Current Query view shows how the first few lines of the query that is currently active in the editor window of your SQL editing application (eg SSMS) would be laid out using the current options.

Editing the format settings

See Options for formatting your code.

Applying a style to your code

  1. Select the style you want to use, then click OK to close the SQL Prompt options dialog box.
  2. In your query editor, highlight the lines of code you want to format, and do one of the following:
  • Press Ctrl+K, Y (hold down Ctrl and press K then Y)
  • Right-click and click Format SQL
  • From the SQL Prompt 4 menu, select Format SQL

If you don't select any code, the formatting will be applied to the entire query.

Importing and exporting styles

You can share formatting styles with other people. SQL Prompt can export all the format settings as a single file, which can then be imported by another SQL Prompt user. This allows you to ensure consistent conventions within your organisation. See Importing and exporting styles.


Importing and exporting styles

If you have saved your code formatting options as a style, you can share that style with other people in your organisation by exporting it.

To export a style

SQL Prompt exports the style as a single file with a .sqlpromptstyle extension.

  1. Set all the formatting options that you want:

    In the SQL Prompt Options dialog box, use the options on the various Format pages to lay out the code the way you want it. Select the Current Query tab to preview how the code currently in your SQL editor would be laid out using these settings.

  2. On the Format > Styles page, click Export.
  3. Browse to where you want to store the file and click Save.

To import a style

  1. In the Format > Styles page, select Import from the Style drop-down list:

  2. Browse to the folder where the .sqlpromptstyle file is located, then click Open.
  3. All the format options will be updated. Your code will not be formatted until you click OK to close the SQL Prompt Options dialog box and then apply the style.


Speeding up your queries with SQL Prompt

In this chapter

  • Inserting snippets
  • Managing snippets
  • Using keyboard shortcuts


Inserting snippets

Snippets enable you to insert pre-defined code into your query editor.

To insert snippet code, type the snippet name and press TAB or any other specified insertion key. In some cases, only TAB is available to insert the snippet (for example when the snippet name could also be the name of an alias you are typing).

To see a list of available snippets, press CTRL+SPACEBAR to display the suggestions box, and select the [image] Snippets category. You can also press CTRL+DOWN to move down the types of suggestion to Snippets.

A preview of the snippet code is displayed in the object definition box. (You can turn off the display of the object definition box - see Setting main behavior options.)

The snippet code is inserted at the same indentation level as your SQL code at the point of insertion.

Using default snippets

SQL Prompt is pre-configured with a number of snippets. For example:

  • ssf inserts a SELECT * FROM fragment
  • cdb inserts a CREATE DATABASE statement and places the insertion point after the CREATE DATABASE fragment.
  • cuci creates a unique clustered index

You can edit or delete the default snippets in the Snippet Manager dialog box.

Creating your own snippets

You can create your own snippets, including specifying the position at which SQL Prompt will place the cursor after it has inserted the snippet of code.

See Managing snippets.


Managing snippets

Snippets enable you to insert pre-defined code into your query editor. To view a list of snippets, on the SQL Prompt menu, select Snippet Manager.

The Snippet Manager dialog box displays a list of snippets, sorted alphabetically. To sort by description, click the Description column header.

Click on a snippet to view the code for that snippet in the Code area.

The snippet code is inserted at the indentation level of your SQL code at the point of insertion.

Creating a snippet from code you have typed

You can create a snippet from code you have typed into the query editor.

Select the code so that it is highlighted, then right-click and select Create Snippet.

The Create New Snippet dialog box is displayed, so that you can edit the snippet name or text if required.

The default snippet name is created automatically from the initial letters of the selected text. You can edit this, if you want to use a different name.

Creating a new snippet

  1. On the SQL Prompt menu, select Snippet Manager.
  2. Click New.
  3. In the Snippet box, type the text that will insert the snippet.
  4. Optionally, type a short description of your snippet in the Description box.

    The description helps you to identify a snippet if you are unsure of the snippet name. You can leave the Description box blank if required.

  5. Type or paste the SQL code in the Code box.

    To specify the insertion point at which you want the cursor to be placed when the snippet is inserted, type $CURSOR$. For example:

    SELECT $CURSOR$ FROM

    places the insertion point after SELECT.

  6. Click Save.

Editing snippets

In the Snippet Manager dialog box, select the snippet that you want to edit and click Edit, or double-click the snippet. Then, in the Edit Snippet dialog box, change the details as required and click Save.

Sharing snippets

You can send your snippets to other SQL Prompt users, or use snippets they send you.

To use a snippet that someone has emailed you, you need to copy the individual snippet file (.sqlpromptsnippet extension) to the Snippets folder:

  1. From the SQL Prompt menu, select Snippet Manager.
  2. In the Snippet Manager dialog box, click Locate on Disk to view the path to the Snippets folder (by default, this is C:\Documents and Settings\<your user name>\Local Settings\Application Data\Red Gate\SQL Prompt 4\Snippets)
  3. Close the Snippet Manager dialog box.
  4. Copy the .sqlprompt snippet file to the Snippets folder.
  5. Re-open the Snippet Manager. The new snippet will be displayed in the list.

Dealing with duplicate snippets

Snippet names are defined by the <Shortcut> tag in the .sqlpromptsnippet file.

If a snippet file you have copied to the Snippets folder uses the same name as an existing snippet, a warning is displayed:

Use the green arrow buttons to locate the next/previous duplicate snippet in the list, then do one of the following:

  • Click Delete to remove the duplicate. (Use the Code preview box to check which snippet you are deleting).
  • Click Edit and change the name in the Snippet box to a unique name.

Note: You can also edit the .sqlpromptsnippet file in a text editor to change the value of the <Shortcut> tag.


Using keyboard shortcuts

Using keyboard shortcuts to select and insert code suggestions in SQL Prompt can dramatically increase the speed of writing your SQL code. You can use the following keyboard shortcuts:

Action | Shortcut
Switch to/from column picker | CTRL + LEFT arrow / CTRL + RIGHT arrow
Move up/down suggestions list | UP arrow / DOWN arrow
Select column in column picker for insertion | SPACE bar
Move up/down the suggestions box filters | CTRL + UP arrow / CTRL + DOWN arrow
Apply format | CTRL + K, Y (Hold Ctrl, then press K then Y)


Managing SQL Prompt behavior

In this chapter

  • Setting main behavior options
  • Customizing inserted code
  • Options for formatting your code
  • Managing connections and memory


Setting main behavior options

You can set various options for how SQL Prompt behaves in the Main > Behavior page of the SQL Prompt Options dialog box.

To display the SQL Prompt Options dialog box, on the SQL Prompt menu, select Options.

Turning off SQL Prompt auto-completion

Clear the Use SQL Prompt check box to stop SQL Prompt offering code suggestions. The suggestions box will not be displayed until you select the check box again.

Turning off the object definition box

When you select a suggestion, the object definition box is displayed to show details of the selected object (for example, its CREATE script if it's a table or a view):

If you don't want the object definition box to be displayed, clear Display object definitions.

Viewing tooltips

Object tooltips display the fully qualified object name for an object when you move the mouse pointer over the object name in your SQL script.

Tooltips can also be displayed for parameters, including parameters for built-in functions.

If you don't want tooltips to be displayed, clear Objects or Parameters under Show tooltips for: in the Tooltips section.

Changing the insertion keys

Insertion keys are the keys that will insert the currently selected suggestion into your code:

Insertion keys are also used to insert snippet code when you type a snippet into your query. The default insertion keys are ENTER and TAB.

Restoring options to their default setting

You can reset the options in SQL Prompt in two ways:

  • for a specific page in the Options dialog box
  • for all options

To restore the options on a single page

Click Restore Defaults in the top right corner:

This will restore only the options on this page to the SQL Prompt defaults.

Options on all other pages will not be affected.

To restore all SQL Prompt options

Click Restore All Defaults in the bottom left corner:

Options on all pages are reset to the SQL Prompt defaults.

Note: Restore All Defaults will also reset all formatting options. If you want to save your formatting options, you should save them as a style. This allows you to reapply them, even if you have restored all defaults. See Using styles to format your code.


Customizing inserted code

To customize how SQL Prompt inserts suggestions into your code, use the Inserted code pages in the SQL Prompt Options dialog box:

To display the SQL Prompt Options dialog box, on the SQL Prompt 4 menu, select Options.

Objects & statements

Options for inserting full syntax for various kinds of statement.

For example, you can specify that SQL Prompt inserts column names, data types and default values for ALTER, EXEC and INSERT statements:

Qualification

Controls whether object or column names are qualified when inserted.

Note: Object and column names are automatically qualified in certain cases, for example:

  • When columns in more than one table have the same name, and not to qualify names would cause ambiguity (only the affected column names are qualified)
  • When you insert JOIN conditions from the suggestions box, both column names are qualified
  • For cross-database and linked server queries, qualified column names may also include the owner name

Aliases

See Working with aliases.


Options for formatting your code

To set formatting options, from the SQL Prompt 4 menu, select Options. The formatting options are all available in the Format group:

Note: If you are using SQL Prompt Standard Edition, the format pages are unavailable; but you can set a number of options for how inserted code is formatted on the Basic (Standard Edition) page:

To review the effect of any changes you make, use the Preview tab:

  • The Sample SQL view shows how an example query would be formatted using the current options.
  • The Current Query view shows how the first few lines of the query that is currently active in the editor window of your SQL editing application (eg SSMS) would be laid out using the current options.

Case

Case options are available in both Standard and Pro editions of SQL Prompt:

  • In Standard edition, case options are on the Format > Basic (Standard Edition) page
  • In Pro edition, case options are on the Format > Case page

Selecting UPPERCASE or lowercase will replace any words you have typed with the selected case, and also insert any suggestions using the selected case:

For example, if UPPERCASE is selected:

  • If you have already typed select or Select in your query, SQL Prompt replaces the word with SELECT.
  • If you type sel or Sel and then insert SELECT from the suggestions box, SQL Prompt inserts SELECT.

Selecting Leave as is will leave the case of any letters you have already typed, but insert the remainder of the word using the case of the last typed letter:

For example:

  • If you type the entire word Select, SQL Prompt leaves the word exactly as typed.
  • If you type Sel, SQL Prompt will insert Select.
  • If you type SEL, SQL Prompt will insert SELECT.

Changing the case in pasted code

When you paste code into your query editor, you can apply your preferred case to that code. This is only available in SQL Prompt Pro Edition.

  1. Set the case options for keywords, functions and data types as required on the Format > Case page of the SQL Prompt Options dialog box.
  2. Click OK to save these settings.
  3. In your query editor, select all the lines of code to which want to apply the case.
  4. From the SQL Prompt 4 menu, select Format SQL.

Note: This will also apply all other format settings (such as indentations, new line settings, and so on) to that code.

Schema statements

Options for formatting new lines and indents in CREATE and ALTER statements.

Data statements

Options for formatting new lines, parentheses and indents in SELECT, INSERT, UPDATE and DELETE statements.

Expressions

Options for formatting parts of statements, for example how Boolean and other types of operators should appear.

Commas & parentheses

Options for formatting where commas should appear in your SQL statements (at the end or beginning of lines), and how they should align with other lines of code.

Tabs & wrapping

Options for tabs and spaces and when to wrap lines.

Formatting SQL in your current editor window

To format the SQL code in the current query editor or the highlighted SQL code fragment, do any of the following:

  • on the SQL Prompt 4 menu, select Format SQL
  • press CTRL+K,Y (hold down CTRL and press K then Y)

In Query Analyzer, press CTRL+SHIFT+Y.

Saving format settings

You can use styles to save your preferences for formatting code. See Using styles to format your code.


Managing connections and memory

SQL Prompt will automatically connect to other databases specified in your query. For example:

In this example, SQL Prompt loads objects from the Northwind database as soon as you type the database name and then continue with your query. For large databases, you may have to wait a few seconds while SQL Prompt loads the objects.

If you do not have permissions to connect to that database, then a message is displayed:

Click Details to see more information about the connection error:

Once you close this message, you can continue to write your query, but no suggestions from that database will appear in the suggestions box.

Note: this message may also be displayed for reasons other than permissions issues, for example, when SQL Prompt doesn't have enough memory to store the objects for this database.

Connecting to different databases in the same session

If you connect to a number of different databases during the same session, SQL Prompt will load all the objects for each database as required, without unloading any of the previously stored objects. This means that you can go back to previous queries and continue to see suggestions for any database to which you have previously connected.

Refreshing suggestions when a database schema changes

If you are writing a query on a database while someone else is making changes to the structure of that database, the suggestions offered by SQL Prompt may become out of date. For example, if someone has added several tables to the database while you have been connected to that database, these tables will not appear in the suggestions box.

To reload the database objects, from the SQL Prompt 4 menu, select Refresh Suggestions or press Ctrl + Shift + D. Any changes to the database structure will now be reflected in the suggestions box.

You should also refresh suggestions when you have created a new object in the current query, and executed that block of code. For example, if you create a table NewObject and execute the statement, then continue to write the query, the NewObject table will not appear in the suggestions box until you refresh the suggestions.

Notes:

  • Refreshing suggestions applies only to the database to which you are currently connected.
  • SQL Prompt will automatically reload database objects when you close your query editor application, then re-open it and connect to a database.

Managing the SQL Prompt memory

SQL Prompt loads database objects into its memory whenever you connect to a database in SSMS or Visual Studio, in order to display suggestions from that database. For databases with large schemas, a progress dialog may be displayed while SQL Prompt loads the objects into memory:

If you connect to several databases with large schemas, this may eventually result in SQL Prompt running out of memory. In this case, a warning dialog box (as illustrated above) will be displayed.

If this message is displayed, you can clear the SQL Prompt memory by clicking Unload All Databases. SQL Prompt will load each database's objects as required.

If you close the message, you can continue to write your query, but no objects from that database will appear in the suggestions box.

Clearing the SQL Prompt memory

To clear the SQL Prompt memory at any time, close your editor application. This unloads all objects from all databases from memory.

Connecting to a linked server

Linked server objects for the current instance are displayed in the suggestions box:

When you select a linked server, SQL Prompt 4 attempts to connect automatically to that server, using the mapped credentials for that linked server object.

  • If you have permissions to connect to the server, suggestions from that server will be loaded into SQL Prompt's memory.
  • If you do not have permissions to connect to that server, a message is displayed. Click Close to continue writing your query; no suggestions from the linked server will appear in the suggestions box.

To stop SQL Prompt from attempting to connect to linked servers:

  1. From the SQL Prompt 4 menu, select Options.
  2. Go to the Suggestions > Types of suggestions page.
  3. Turn off Enable linked server support.