How to work with SQL aliases in dbForge Studio for SQL Server

dbForge Studio can automatically generate aliases for all table objects, such as a table, a view, a table-valued function, and a synonym, referenced in the FROM list of the SQL document. You can generate custom aliases from the Options dialog.

The guide walks through the following topics:

• Automatic alias assignment
• Custom alias mapping
• Alias refactoring
• Alias masks
• Rules to define a condition in the Condition column
  • Case 1: Generate an alias for the database object regardless of prefixes
  • Case 2: Generate an alias for the database object from a specific schema
  • Case 3: Generate an alias for the database object with multiple prefixes
  • Case 4: Use an asterisk (*) for any characters in the condition
  • Case 5: Use an asterisk (*) in the identifier name
• Rules to define an action in the Action column
  • Case 1: Use one element in the Action column
  • Case 2: Add characters before or after one element
  • Case 3: Use multiple elements in the Action column
  • Case 4: Exclude any characters from the identifier element
  • Case 5: Add a static set of characters to the action
• Mask prioritization

To open the Options dialog:

1. On the ribbon, select Tools > Options.

2. In the Options dialog that opens, go to Text Editor > Code Completion > Alias.

Automatic alias assignment

When the Generate alias on commit checkbox is selected, the Studio operates as follows:

• Adds aliases to tables, views, table-valued functions, and synonyms: As the user selects a database object from the suggestion list, dbForge Studio automatically assigns an alias to it.

• Adds table alias to column names: When the user selects all columns using the asterisk (*) wildcard or individually selects specific columns from the suggestion list, dbForge Studio adds a table alias to each column name.

The following rules are used when creating aliases:

• If possible, dbForge Studio 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, dbForge Studio adds symbols 1, 2, … n to the generated alias to avoid ambiguity.

Custom alias mapping

Users can manually define conditions that determine when an alias should be inserted while writing a query using the Completion List in a SQL document.

To add a custom alias:

1. On the Tools menu, select Options.

2. In the Options dialog that opens, navigate to Text Editor > Code Completion > Alias.

3. In the Condition column of the Alias page, specify the database object name (you may use a mask) to which you want to assign an alias.

4. In the Action column, specify the custom alias you prefer.

5. Select OK to save the changes.

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

Note

Custom aliases have precedence over automatically created ones.

Alias refactoring

To rename an alias:

1. Right-click the required alias and select Rename, 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 you to Press F2 to preview changes or Enter/Tab to apply.

3. Press F2 to open the Preview Changes - Rename dialog and preview code changes.

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

Alias masks

dbForge Studio offers several alias masks for managing custom alias mapping. When assigning aliases, you can use the alias masks to specify conditions and actions. For example, you can assign different aliases to objects with the same name.

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> - specifies an exact match for the object name “product” with “schema” as its prefix.
• <schema*>.<product*> - matches object names where the prefix starts with “schema” and the object name starts with “product.”
• <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 the 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 specific condition, no alias will be generated for that condition.

Rules to define a condition in the Condition column

In the Condition column, you can define the criteria under which an alias will be inserted based on the rule specified in the Action column. This occurs when an identifier, such as a table, view, table-valued function, or synonym, is inserted into a SQL document from the Completion List.

The criteria from the Condition column must be fully met for the inserted identifier to generate a value specified in the Action column. According to the syntax, each identifier must be enclosed in angle brackets < >.

Note

The condition is case-insensitive, meaning it can be written in either lowercase or uppercase. The rule is applied based on the identifier name, regardless of its case.

Consider several examples to understand how to create custom aliases in the Options dialog.

Case 1: Generate an alias for the database object regardless of prefixes

Generate a custom NewAlias alias for the Employee table, regardless of servers, schemas, or databases to which it belongs. The syntax must be as follows:

When writing a query in a SQL document and inserting Employee from the Completion List, the tool automatically generates NewAlias and adds it to each inserted Employee identifier. This means that the condition will be applied to every Employee identifier, no matter what database and schema it belongs to. For example:

SELECT * FROM HumanResources.Employee NewAlias

SELECT * FROM Person.Employee NewAlias

SELECT * FROM dbo.Employee NewAlias

Case 2: Generate an alias for the database object from a specific schema

Generate a custom NewAlias alias for the Employee table that belongs to the HumanResources schema. The syntax must be as follows:

In this case, the condition will be applied only to the Employee identifier from the HumanResources schema. The generation of the NewAlias alias for different schemas will be as follows:

SELECT * FROM HumanResources.Employee NewAlias

SELECT * FROM Person.Employee e

SELECT * FROM dbo.Employee e

Case 3: Generate an alias for the database object with multiple prefixes

The Condition column can include as many prefixes as the script uses. However, the maximum number of prefixes allowed is three. For example:

• <server name or linked server name>.<database name>.<schema name>.<object name> uses three prefixes for a table.
• <database name>.<schema name>.<object name> uses two prefixes for a table.
• <schema name>.<object name> uses one prefix for a table.

This means that if the prefixes are specified, an alias will be generated only if these prefixes exist in the statement in a SQL document.

For example, if the Condition column has the following criteria as shown in the screenshot:

In this case, the NewAlias alias is generated for the Address table only when the expression includes all prefixes specified in the Condition column, such as the linked server name, database name, and schema name:

SELECT * FROM LinkToSQLServer2022.AdventureWorks2022.Person.Address NewAlias

However, if the expression does not include the LinkToSQLServer2022 prefix, the NewAlias alias will not be generated:

SELECT * FROM AdventureWorks2022.Person.Address a

Case 4: Use an asterisk (*) for any characters in the condition

In the Condition column, you can use a wildcard (*) to represent any characters before or after identifiers. The wildcard can be used anywhere within angle brackets < >.

If we specify the following condition as shown in the screenshot:

In this case, the NewAlias alias is generated for all servers and databases, which contain the Address identifier in the Person schema. For example:

SELECT * FROM LinkToSQLServer2022.AdventureWorks2022.Person.Address NewAlias

SELECT * FROM LinkToSQLServer2022.AdventureWorks2019.Person.Address NewAlias

Case 5: Use an asterisk (*) in the identifier name

If the identifier includes an asterisk in its name as shown in the screenshot:

In this case, the NewAlias alias is generated for all identifiers whose name starts with Business and belongs to the Person schema. For example:

SELECT * FROM Person.BusinessEntity NewAlias

SELECT * FROM Person.BusinessEntityAddress NewAlias

SELECT * FROM Person.BusinessEntityContact NewAlias

Note

The asterisk (*) can be used in any part of the identifier within < >.

Rules to define an action in the Action column

In the Action column, you can do the following:

• Specify any alias characters that will be automatically generated after inserting an identifier from the Completion List for the corresponding condition set in the Condition column.
• Specify the identifier part to be excluded from the alias generation algorithm by declaring this part in square brackets [ ].
• Specify the specific identifier for which an alias will be generated, such as server name identifier, database, schema, or (and) object.
• Specify No Alias. This means that an alias will not be generated for the conditions specified in the Condition column.

The simple case can be as follows:

In this case, the NewAlias alias is generated for the ContactType table from the Person schema.

SELECT * FROM Person.ContactType NewAlias

Case 1: Use one element in the Action column

If the Condition column contains only one element, for example, an object identifier, the Action column will have the <1> value by default. This means that if you specify <1> in the Action column for this condition, an alias will be generated according to the standard alias generation algorithm.

In this case, the result will be as follows:

SELECT * FROM Person.ContactType ct

Case 2: Add characters before or after one element

Adding characters before or after <1> will automatically add those characters to the generated alias. For example, add NewAlias after <1>:

In this case, the alias will be generated:

SELECT * FROM Person.ContactType ctNewAlias

Writing NewAlias before <1> will generate the alias as follows:

In this case, the alias will be generated:

SELECT * FROM Person.ContactType NewAliasct

Case 3: Use multiple elements in the Action column

If the Condition column contains one condition where the prefixes include the server, database, schema, and table, it looks as follows:

<LinkToSQLServer2022>.<AdventureWorks2022>.<Person>.<Address>

The conditional numbering of each element occurs from left to right. This means that the Action column will take these elements as <1>, <2>, <3>, and <4>, respectively.

To specify an action for an identifier in the Condition column, it is important to know its conditional number in the Condition column.

For example, if an alias should be generated based on the database name, i.e. AdventureWorks2022, specify <2>, which is a conditional number of the database, in the Action column:

In this case, the following alias will be generated:

SELECT * FROM LinkToSQLServer2022.AdventureWorks2022.Person.Address aw

The aw alias was generated for AdventureWorks2022 using the standard alias generation algorithm, i.e. CamelCase for the identifier.

If it is necessary to generate an alias based on the schema name and table, i.e. Person and Address, specify <3> and <4> in the Action column:

In this case, the pa alias will be generated based on the capitalization of the Person and Address identifiers.

SELECT * FROM LinkToSQLServer2022.AdventureWorks2022.Person.Address pa

So, if <*>.<*>.<*>.<*> is specified in the Condition column, while <1><2><3><4> will be set in the Action column, the aliases will be generated according to the common algorithm for all elements, for example:

SELECT * FROM LinkToSQLServer2022.AdventureWorks2022.Person.BusinessEntityAddress ltsawpbea

SELECT * FROM LinkToSQLServer2022.AdventureWorks2022.Sales.Customer ltsawsc

SELECT * FROM LinkToSQLServer2022.DWQueue.dbo.MessageQueue ltsddmq

Case 4: Exclude any characters from the identifier element

To exclude a specific part of the identifier from any element defined in the Condition column, specify the part to be excluded in the Action column within angle brackets < >.

According to the syntax rules, the excluded part must be enclosed in square brackets [ ].

For example, exclude Add from the fourth identifier in this expression:

<LinkToSQLServer2022>.<AdventureWorks2022>.<Person>.<Address>

The fourth identifier is Address. So, the following must be written in the Action column:

Thus, the next character after Add in the fourth identifier will be r. The alias will be generated as follows:

SELECT * FROM LinkToSQLServer2022.AdventureWorks2022.Person.Address r

Following the same rule, conditions can be added for other identifiers. For example, add the alias generation based on the second identifier, i.e. a database name, to the existing action <[Add]4> while excluding Adv.

The Action column must contain the following:

The next character after Adv is e. However, according to alias generation rules, if an uppercase letter appears in the subsequent characters of the identifier, the alias will be generated based on that uppercase letter rather than the next character after the exclusion.

Thus, if the identifier is Adventureworks2022, the alias will be generated as follows based on the specified action:

SELECT * FROM LinkToSQLServer2022.Adventureworks2022.Person.Address er

However, since the identifier is AdventureWorks2022 (where an uppercase letter W appears immediately after the excluded part), the alias generated based on the specified action will be wr.

SELECT * FROM LinkToSQLServer2022.AdventureWorks2022.Person.Address wr

So, in this case, the following alias will be generated for the <[Adv]2><[Add]4> action:

SELECT * FROM LinkToSQLServer2022.AdventureWorks2022.Person.Address wr

Case 5: Add a static set of characters to the action

For example, add a static set of characters, NewAlias, to the end of the action.

In this case, the alias will be the following:

SELECT * FROM LinkToSQLServer2022.AdventureWorks2022.Person.Address wrNewAlias

Mask prioritization

User-defined (custom) masks have precedence over automated alias mapping.

Alias masks specified in the grid have top-down priority. The condition with the highest priority appears at the top of the list in the Condition column.

When multiple conditions are met, the condition located higher in the list takes priority. When multiple conditions are met, the condition located higher in the list takes priority.

For example, there are two conditions:

The SELECT statement is as follows:

SELECT * FROM Person.BusinessEntity

Both conditions can be applied to insert an identifier whose name starts with Business. However, since the <Person>.<Business*> condition appears higher in the list, the Alias1 alias will be generated:

SELECT * FROM Person.BusinessEntity Alias1

Now, we change the order of conditions in the list as follows:

In this case, when <Person>.<Business*> is placed below the <Business*> condition that can also be applied to the expression, the Alias2 alias will be generated since it is higher in the list:

SELECT * FROM Person.BusinessEntity Alias2

The following table demonstrates an example of custom alias mapping. The <*Person*> mask condition has a higher priority than <Product*>:

• 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 whose name starts with Product, and a schema name starts with Sales.

Helpful materials

SQL Aliases: Improving Query Efficiency and Clarity

Find out what SQL aliases are and how to use them to enhance the efficiency of SQL scripts.

Want to Find out More?

Overview

Take a quick tour to learn all about the key benefits delivered by dbForge Studio for SQL Server.

All features

Get acquainted with the rich features and capabilities of the Studio in less than 5 minutes.

Request a demo

If you consider employing the Studio for your business, request a demo to see it in action.

Ready to start using dbForge Studio for SQL Server?

Download a free 30-day trial

• Previous
• Next