Click or drag to resize

SA0082 : Consider prefixing column names with table name or table alias

The topic describes the SA0082 analysis rule.

Message

Consider prefixing column names with table name or table alias

Description

The rule checks SELECT,UPDATE and DELETE statements which use more than one table source and reference columns which are not prefixed with table name or table alias.

Consider prefixing column names with table name or alias in order to improve readability and avoid ambiguity.

Note Note

Additional violations reported by the rule:

  • Usage of non-existing table alias

  • Aliased column missing in the aliased table source.

Scope

The rule has a Batch scope and is applied only on the SQL script.

Parameters
NameDescriptionDefault Value
RuleSuppressMark

Suppress rule reporting a rule violation in case a comment containing the value of this parameter is found close to the source of violation.

IGNORE:SA0082

IgnoreSingleTableSources

Ignore non aliased columns in statements which reference single table source.

yes

CheckAliasedColumnExists

If parameter value is set to 'yes', the aliased columns will be checked for existing in the parent table.

yes

Remarks

The rule requires SQL Connection. If there is no connection provided, the rule will be skipped during analysis.

Categories

Design Rules

Additional Information

There is no additional info for this rule.

Example Test Script
 1SELECT  v.[BusinessEntityID]
 2      , v.[Name]
 3      , ct.[Name] AS [ContactType]
 4      , p.[Title]
 5      , [FirstName]
 6      , [MiddleName]
 7      , [LastName]
 8      , [Suffix]
 9      , pp.[PhoneNumber]
10      , pnt.[Name] AS [PhoneNumberType]
11      , ea.[EmailAddress]
12      , p.[EmailPromotion]
13FROM  [Purchasing].[Vendor] AS v
14INNER JOIN [Person].[BusinessEntityContact] AS bec
15ON  bec.[BusinessEntityID] = v.[BusinessEntityID]
16INNER JOIN [Person].ContactType AS ct
17ON  ct.[ContactTypeID] = bec.[ContactTypeID]
18INNER JOIN [Person].[Person] AS p
19ON  p.[BusinessEntityID] = [PersonID]
20LEFT OUTER JOIN [Person].[EmailAddress] AS ea
21ON  ea.[BusinessEntityID] = p.[BusinessEntityID]
22LEFT OUTER JOIN [Person].[PersonPhone] AS pp
23ON  pp.[BusinessEntityID] = p.[BusinessEntityID]
24LEFT OUTER JOIN [Person].[PhoneNumberType] AS pnt
25ON  pnt.[PhoneNumberTypeID] = pp.[PhoneNumberTypeID];
26
27
28DELETE FROM edh
29FROM  HumanResources.EmployeeDepartmentHistory AS edh
30WHERE  ShiftID = 5 AND
31       edh.StartDate IN( '2010-05-22', '2010-05-22' ) AND
32       DepartmentID LIKE '43 '

Analysis Results

 MessageLineColumn
1SA0082 : The column [FirstName] is not prefixed with table alias name.58
2SA0082 : The column [MiddleName] is not prefixed with table alias name.68
3SA0082 : The column [LastName] is not prefixed with table alias name.78
4SA0082 : The column [Suffix] is not prefixed with table alias name.88
5SA0082 : The column [PersonID] is not prefixed with table alias name.1927
6SA0082 : The column [ShiftID] is not prefixed with table alias name.307
7SA0082 : The column [DepartmentID] is not prefixed with table alias name.327
See Also

Other Resources