Managing Aliases

You can set SQL Prompt so that it automatically assigns an alias to each table and view that is 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.

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], pv.[ProductID]
FROM [Purchasing].[ProductVendor] AS pv
INNER JOIN [Purchasing].[ProductVendor] AS pv2
ON pv.[ProductID] = pv2.[ProductID]
WHERE pv.[VendorID] <> pv2.[VendorID]

To manage alias assignment, on the SQL Prompt menu, click Options, and then click the Auto Insert tab and the Aliases page.

Alias assignment

When the Enable alias assignment 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 * 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 [Person].[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:

SELECT [FirstName]
FROM [Person].[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, clear the Learn aliases when typing 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 opening files or pasting text check box. You are not recommended to select this option if you are working with large scripts.

User-defined 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.

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

SELECT [FirstName]
FROM [Person].[Contact] AS Con

To add a user-defined alias, under User-defined Aliases, click New. Then, on the Alias Properties dialog box, type the name of the table or view in the Object name box and the alias in the Alias box, and then click Save.

To delete a user-defined alias, select the alias that you want to delete, and 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.

To add a prefix to the list, under Prefixes to Ignore, click New. Then, on the Prefix to Ignore dialog box, type the prefix in the Prefix box and click Save.

To delete a prefix from the list, select the prefix that you want to delete, and click Delete.