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.
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.
Where possible, SQL Prompt generates aliases using the first letter of the table or view name. SQL Prompt also takes into account:
TBL_Contact is assigned the alias tc
hyphenated-tablename is assigned the alias ht
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
The Define New Alias dialog box is displayed.
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.
The Prefix to Ignore dialog box is displayed.
© Red Gate Software Ltd 2007. All Rights Reserved.