Working with aliases
Published 29 O 2019
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:
- On the SQL Prompt menu, click Options.
- 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 aliastc
- hyphens
hyphenated-tablename
is assigned the aliasht
- case
MixedCase
is assigned the aliasmc
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:
- Under Custom aliases, click New.
- 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.
- 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:
- Under Prefixes to ignore, click New.
- In the Prefix to Ignore dialog box, type the name of the prefix.
- 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: