SQL Prompt 10

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. On 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, to specify the user-defined alias Con for the Contact table, type the following in the Define Custom Alias dialog box:

SQL Prompt will then assign 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 there is a table called TBL_Orders, SQL Prompt considers only Orders when assigning an alias for the table name:

  • Prefixes are not case-sensitive. Specifying tblTBL, 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.

Didn't find what you were looking for?