Setting Automatic SQL Insertion

See Also

You can set SQL Prompt so that it automatically inserts SQL in your query editor after certain keywords or so that it automatically inserts closing characters when you type opening characters.

To set the auto-insertion options, on the SQL Prompt menu in your query editor, click Options, and then click the Auto Insert tab.

To reset the options on the Auto Insert tab to their default values, click Restore Defaults.

Automatic SQL Insertion

The options that insert SQL in your query editor are grouped together under Automatic SQL Insertion.

Enable TAB to expand columns after SELECT *

When you write a SELECT * statement and you specify the tables or views in the FROM clause, you can complete the column list by placing the insertion point after the * and pressing TAB.

SQL Prompt also completes the column list when you type SELECT table.* and then press TAB.

Note the following:

• The columns are inserted in the order in which they were defined.
• The columns are inserted on separate lines.

  You can clear the Use new line for each column option to insert the columns on a single line.

• The columns are qualified if any of the following apply:
  • you have typed SELECT table.*
  • you have selected the Qualify column names option
  • you have specified more than one table in the FROM clause

  When alias assignment is turned off, columns are qualified with the table name and the owner (or schema) name. For example:

  SELECT [Person].[Address].[AddressID],
         [Person].[Address].[AddressLine1],
         [Person].[Address].[AddressLine2],
         [Person].[Address].[City],
         [Person].[Address].[StateProvinceID],
         [Person].[Address].[PostalCode],
         [Person].[Address].[rowguid],
         [Person].[Address].[ModifiedDate],
         [Person].[AddressType].[AddressTypeID],
         [Person].[AddressType].[Name],
         [Person].[AddressType].[rowguid],
         [Person].[AddressType].[ModifiedDate]
  FROM [Address], [AddressType]
  

  When alias assignment is turned on, columns are qualified with the table alias. For example:

  SELECT [a].[AddressID],
         [a].[AddressLine1],
         [a].[AddressLine2],
         [a].[City],
         [a].[StateProvinceID],
         [a].[PostalCode],
         [a].[rowguid],
         [a].[ModifiedDate],
         [at].[AddressTypeID],
         [at].[Name],
         [at].[rowguid],
         [at].[ModifiedDate]
  FROM [Address] AS a, [AddressType] AS at
  

  Note that SQL Prompt does not assign aliases until you have specified a column or * to select all columns.

• The columns are inserted according to the formatting you have specified on the Style tab; by default, SQL Prompt surrounds identifiers with brackets [ ].

To prevent SQL Prompt from completing the column list, clear the Enable TAB to expand columns after SELECT * check box.

Use new line for each column

By default, when SQL Prompt completes column lists each column is inserted on a new line. Clear the Use new line for each column check box to set SQL Prompt so that it inserts columns on a single line when you:

• expand the column list after SELECT * or SELECT table.*
• select columns using the column picker

Insert object definition on ALTER

SQL Prompt inserts the object definition for a function, stored procedure, or view when you type ALTER FUNCTION, ALTER PROCEDURE, or ALTER VIEW, and you select the required candidate.

If you do not want SQL Prompt to insert object definitions, clear the Insert object definition on ALTER check box.

Functions and Stored Procedures

By default, SQL Prompt inserts parameters when you type EXEC and select a stored procedure or scalar function from the candidate list. SQL Prompt also inserts the associated data types as comments, for example:

@StartProductId = ? --int

If you do not want SQL Prompt to insert hints for the data types, clear the Insert hints for data types check box.

If you do not want SQL Prompt to insert parameters, clear the Insert parameters for functions and stored procedures check box.

Automatic Closing Characters

When you type opening characters, for example a single quotation mark ( ' ), SQL Prompt automatically inserts the corresponding closing character.

You can set SQL Prompt to automatically insert the following closing characters:

• Parenthesis ( ) )
• Bracket ( ] )
• Single quotation mark ( ' )
• Double quotation mark ( " )
• Comment mark ( */ )

If you do not want SQL Prompt to insert a particular closing character, clear its check box.

 

 

 

---

© Red Gate Software Ltd 2007. All Rights Reserved.