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.

Note Note

Additional violations reported by the rule:

  • Usage of non-existing table alias

  • Aliased column missing in the aliased table source.
How to fix

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

Scope

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

Parameters
Name Description Default Value
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.

Effort To Fix
5 minutes per issue.
Categories

Design Rules, Code Smells

Additional Information

There is no additional info for this rule.

Example Test SQL
SQL
Copy
 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
  Message Line Column
1 SA0082 : The column [FirstName] is not prefixed with table alias name. 5 8
2 SA0082 : The column [MiddleName] is not prefixed with table alias name. 6 8
3 SA0082 : The column [LastName] is not prefixed with table alias name. 7 8
4 SA0082 : The column [Suffix] is not prefixed with table alias name. 8 8
5 SA0082 : The column [PersonID] is not prefixed with table alias name. 19 27
6 SA0082 : The column [ShiftID] is not prefixed with table alias name. 30 7
7 SA0082 : The column [DepartmentID] is not prefixed with table alias name. 32 7
See Also

Other Resources

Analysis Rules