Notes & articles

Customizing SQL Prompt to write SQL code more efficiently

SQL Prompt speeds up the writing of SQL code by offering a list of database objects and SQL statements to be inserted, based on the context of what you type in your query editor.

This article explains how you can further speed up the writing of your code, and customize the code completion behavior, by setting various options in SQL Prompt:

• Qualify column names with the table name
• Qualify database object names with the schema (owner) name
• Assign aliases automatically
• Create snippets to insert predefined blocks of code

All these settings are available in the SQL Prompt Options dialog box. When you have changed the settings you want, click OK to save them and close the dialog box.

To display the SQL Prompt Options dialog box

From the SQL Prompt menu, for example in SQL Server Management Studio, select Options:

Qualifying column names with the table name

You can save time when writing SQL by setting SQL Prompt to add the table name to each column you insert; this means you don't have to type the table name each time.

When you select a column from the candidate list

SQL Prompt inserts the code as follows:

SELECT [AddressLine1] FROM [Address]

You may want the column name to always include the table name as part of its definition:

SELECT [Address].[AddressLine1] FROM [Address]

To specify that SQL Prompt should always insert column names

1. Display the SQL Prompt Options dialog box.
2. Select the Inserted Candidates tab, then select Formatting.
3. Select the Qualify column names check box.

   

Qualify object names with the owner name

You can save time when writing SQL by setting SQL Prompt to add the owner name to each object you insert; this means you don't have to type the object name each time.

When you select a database object (for example, a view or a table) from the candidate list, SQL Prompt inserts the code as follows:

SELECT * FROM [Address]

You may want the object name to always include the owner name as part of its definition:

SELECT * FROM [dbo].[Address]

To specify that SQL Prompt should always insert object names

1. Display the SQL Prompt Options dialog box.
2. Select the Inserted Candidates tab along the top of the dialog box, then select the Formatting page from the left.
3. Select the Qualify object names check box.

   

Assigning aliases automatically

You can specify that SQL Prompt automatically assigns an alias to a table or view when you first reference it in a query. SQL Prompt inserts the alias every time you reference the table or view; this means you do not have to type the alias manually, and reduces key presses.

SQL Prompt uses the first letter of the table or view name to create the alias:

SELECT a.[AddressLine1] FROM [dbo].[Address] AS a

Whenever that object is referred to in the query, the alias will be used.

SQL Prompt uses two letters as the alias when the table or view name comprises two words separated by a hyphen, underscore or by the use of a different case. For example:

SELECT AT.[Name] FROM [Person].[AddressType] AS at

To automatically insert an alias for a table or view

1. Display the SQL Prompt Options dialog box.
2. Select the Auto Insert tab, then select Aliases from the left.
3. Select the Enable alias assignment check box.

   

There are a number of options you can set to configure how aliases are inserted into your code:

• When Include AS in alias definition is selected, SQL Prompt creates the alias using the AS keyword. Clear the check box if you do not want SQL Prompt to include the AS keyword when it assigns aliases.
• When Learn aliases when typing is selected, if you assign a different alias to a table or view name, SQL Prompt remembers the alias you assigned. For example, if SQL Prompt assigns the alias c to the table Contact, you can overwrite the c with a different alias.
• When Learn aliases when opening files or pasting text is selected, SQL Prompt learns aliases from SQL statements that you have pasted into your query editor window or from SQL that you have loaded from a file. You are recommended to clear this option if you are working with large scripts.

Using snippets

A snippet inserts a block of code into your query editor when you type the snippet name. For example, typing

atac

and pressing ENTER inserts the following code:

ALTER TABLE | ALTER COLUMN

This snippet is provided by default with SQL Prompt. To view the list of default snippets or to create your own snippets, use the SQL Prompt Options dialog box:

1. Display the SQL Prompt Options dialog box.
2. Select the Snippets tab.

   The Snippets page lists all the default snippets available in SQL Prompt in alphanumeric order. To sort by category or description, click the column header.

   

If the snippet you want is not in the list, you can create your own snippet. To create a snippet:

1. Click New.
2. Type a name for your snippet in the Snippet box. The snippet name is the text that you will type in your query editor window when you want to use the snippet.
3. 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, use the variable $CURSOR$. For example:

   SELECT $CURSOR$ FROM

   places the insertion point after SELECT.

4. Click Save.

Customizing how snippets are inserted

By default, to insert snippet code, you type the snippet name into the query editor then press the TAB key. You can set some options for how the code inserted.

To insert snippets without using the candidate list

If you want to select the snippet code from the candidate list, rather than insert it using the TAB key, clear the Insert snippets without using the candidate list check box.

When you type a snippet, a preview of the code for the selected snippet will be displayed in the Schema panel. Press ENTER to insert the snippet code.

To insert snippets without using the current indentation level

By default, snippet code is inserted at the indentation level of your SQL code at the point of insertion:

If you do not want the snippet code to be indented, clear the Insert snippet at current indentation level check box.

Snippet code will be inserted without being indented: