Managing Aliases

You can set SQL Prompt so that it automatically assigns aliases to tables and views that are referenced in a SQL statement. You can also define custom aliases for tables and views. If your table or view names contain prefixes, you can set SQL Prompt so that it ignores those prefixes when assigning aliases.

To manage alias assignment, on the SQL Prompt menu in your query editor, click Options, and then click the Aliases tab.

Alias Assignment

When Alias Assignment is On, 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 * to select all columns.

For example, if you select the column FirstName and then the table Contact, SQL Prompt creates the alias c where c represents the table name Contact.

SELECT [FirstName]
FROM [Contact] AS c

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.

For example, with this option cleared:

SELECT [FirstName]
FROM [Contact] c

When SQL Prompt assigns an alias, it remembers it for use in subsequent queries in the current query editor window. The candidate list 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.

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; SQL Prompt remembers this alias the next time you reference the table Contact. If you do not want SQL Prompt to remember aliases that you assign, clear the Learn aliases as I type check box.

To set SQL Prompt so that it learns aliases from SQL statements that you have pasted into your query editor window or from SQL that you have loaded from a file, select the Learn aliases when I open files or paste text check box. You are not recommended to select this option if you are working with large scripts.

User-defined Aliases

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

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

SELECT DISTINCT [pv].[VendorID], [pv2].[ProductID]
FROM [Purchasing].[ProductVendor] AS pv
INNER JOIN [Purchasing].[ProductVendor] AS pv2
ON [pv].[ProductID] = [pv2].[ProductID]
WHERE [pv].[VendorID] <> [pv2].[VendorID]

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.

For example, if you specify the user-defined alias Con for the table Contact, SQL Prompt assigns the alias as follows:

SELECT [FirstName]
FROM [Contact] AS Con

Adding user-defined aliases

Under User-defined Aliases, click New.
The Define New Alias dialog box is displayed.
Type the name of the table or view in the Name box.
Type the alias in the Alias box.
Click Save.

Deleting user-defined aliases

Under User-defined Aliases, select the alias that you want to delete.
Click Delete.

Prefixes to Ignore

You can specify prefixes that SQL Prompt will ignore when assigning aliases for column, table, or view names.

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.

Adding prefixes to the list

Under Prefixes to Ignore, click New.
The Prefix to Ignore dialog box is displayed.
Type the prefix in the Prefix box.
Click Save.

Deleting prefixes from the list

Under Prefixes to Ignore, select the prefix that you want to delete.
Click Delete.