Operations with aliases

This topic describes the following features:

SQL Complete automatically assigns an alias to each table, view, table-valued function, and synonym that is referenced in a SQL statement.

It is possible to customize the generation of aliases as follows:

  1. In the SQL Complete menu, click Options.
  2. In the dbForge SQL Complete Options dialog, go to the Aliases tab.

Options

Alias assignment

When the Generate alias on commit checkbox is selected, SQL Complete assigns aliases to tables, views, table-valued functions, and synonyms that are referenced in a SQL statement, provided that a list of columns is specified, or * is used to select all columns.

To include the AS keyword when assigning aliases, enable the Automatically generate AS clause option.

Where possible, SQL Complete generates aliases using the first letter of the corresponding objects. To get a better understanding of how aliases are generated, consider the following example:

  • Alias tc will be assigned to the names with MixedCase, e.g. TableCustomer
  • Alias tc will be assigned to the names with underscores, e.g. Table_Customer
  • Alias tc will be assigned to the names with hyphens, e.g. Table-Customer

SQL Complete ignores such prefixes as tbl, v, fnc at the beginning of the name and assigns aliases not taking these prefixes into account, for example:

  • Alias cs will be assigned to the names with the tbl prefix, e.g. tblCustomerSupport

If an assigned alias name matches a keyword, the alias name is wrapped in square brackets. For example, if the table object name is GeneralObjects, the alias name will be [go].

SQL Complete creates aliases with additional symbols 1, 2…n to avoid ambiguity, for instance, in SELF JOINS:

Additional symbols

Custom aliases

If the aliases that SQL Complete auto-generates do not satisfy the required naming conventions, there is always an opportunity to specify custom aliases for tables, views, table-valued functions, synonyms, databases, servers, and linked servers.

To add a custom alias:

  1. Go to the SQL Complete menu, click Options.
  2. In the Options window, navigate to the Alias tab.
  3. On the Alias tab, select the required options.
  4. Define an identifier in the Condition column using one of the condition masks, as required.
  5. Define the alias name in the Action column using one of the action masks.
  6. Click OK.

When a custom alias is created, SQL Complete assigns the alias name to the object that is referenced in the SQL statement according to the specified alias mask.

Alias masks

SQL Complete offers a number of alias masks for managing custom alias mapping. Alias masks can be used to specify conditions and actions when assigning aliases. For example, different aliases can be assigned to objects with the same name.

The following list demonstrates examples of condition masks:

<product> - exact match

<*product> - matches object names ending with “product”

<product*> - matches object names starting with “product”

<*product*> - matches object names including “product”

<*> - matches any valid object name

<schema>.<product> - exact match with the object name product using schema as a prefix

<schema*>.<product*> - matches object names starting with product with a prefix starting with schema

<database>.<schema>.<product> - matches the object name product with a database and schema in the prefix

The following list demonstrates examples of action masks:

Id<1> - adds prefix Id to a generated alias

<1>.<2> - generates an alias with a prefix

<[Dept]1> - excludes Dept from a generated alias

No Alias - when No Alias is specified in the Action column for a certain condition, no alias will be generated for that condition

Mask prioritization

User-defined (custom) masks have precedence over the automated alias mapping. Alias masks specified in the grid have top-down priority. For example, in the following table demonstrating an example custom alias mapping, the <Person> mask condition has a higher priority than <Product*>:

Condition Action
<Person> <alias1>
<Product*> <alias2>
<Sales>.<Product*> <alias2>

alias1 will be assigned to all objects including Person

alias2 will be assigned to all objects starting with Product

alias3 will be assigned to all objects starting with Product with parent Sales

Configuration of alias settings

If you want to create a custom alias, navigate to the SQL Complete menu and select Options.

On the Alias tab, you can perform different alias-related tasks, such as:

  • Manipulate the alias generation:

    • Generate alias on commit
    • Generate aliases in UPPER case
    • Automatically generate AS clause
  • Create, edit, or delete alias conditions and actions
  • Specify a prefix to be ignored or added to an alias
  • Match identifiers ending and starting with a specific value

Alias refactoring

SQL Complete allows renaming aliases in queries automatically. To rename an alias:

  1. Right-click the required alias and select the Rename command from the shortcut menu or select the alias and press F2 - the alias will be highlighted.

  2. Type a new name for the alias in the SQL editor window. As you type, a tooltip appears instructing to Press F2 to preview changes or Enter/Tab to apply.

  3. Press F2 to open the Preview Changes - Rename dialog window and preview code changes; to apply changes press the Apply button.

  4. Alternatively, press Enter/Tab to apply changes in the code.

For more information, refer to Rename aliases.