Managing Aliases

SQL Complete can automatically generate aliases for each table object (table, view, table-valued function, synonym) referenced in the FROM list of your SQL document. To disable automatic alias generation, clear the Generate alias on commit option. If there are prefixes in the names of your table objects, you can setup SQL Complete so that it ignores those prefixes when assigning aliases.

The following rules are used when creating aliases:

  • If possible, SQL Complete generates aliases using the first letter of a table object name.
  • For names with underscores:
    for TBL_Address alias ta is assigned

  • For names with hyphens:
    for Tbl-address alias ta is assigned

  • For names with CamelCase:
    for TblAddress alias ta is assigned

  • For names with numbers:
    for 111 alias a is assigned

  • If a generated 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].

  • If a newly generated alias coincides with an existing one, SQL Complete adds symbols 1, 2,…n to the newly generated alias to avoid ambiguity.

Alias Refactoring

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.

Custom Alias Mapping

SQL Complete allows users to manually assign aliases to an identifier (table, view, table-valued function, synonym) referenced in an SQL document. Aliases can also be assigned to databases, servers, and linked servers.

To add a custom alias:

  1. In the SQL Complete menu, click Options.

  2. In the Options window, navigate to the Alias tab. The Alias page opens.

  3. On the Alias page, in Settings, you can select one or more of the available options.

  4. Define an identifier in the Condition column using one of the condition masks, as required.

  5. Define an alias name in the Action column using one of the action masks.

  6. Click OK.

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

Custom aliases have precedence over automatically created ones.

Alias Masks

SQL Complete offers a number of alias masks for managing custom alias mapping. You can use the alias masks to specify conditions and actions when assigning aliases. For example, you can assign different aliases for same-named objects.

The following list demonstrates examples of the 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 the action masks:

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

<1>.<2> - generates 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.