Click or drag to resize

SA0136 : Use fully qualified object names in SELECT, UPDATE, DELETE, MERGE and EXECUTE statements

The topic describes the SA0136 analysis rule.

Message

Use fully qualified object names in SELECT, UPDATE, DELETE, MERGE and EXECUTE statements

Description

The rule enforces usage of fully qualified object names in SELECT,UPDATE,DELETE or INSERT and EXECUTE statements.

Scope

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

Parameters
NameDescriptionDefault Value
AllowMissingIdentifiers

Allow object names have omitted schema,database or server identifiers ('server.database..object', 'server..schema.object', 'server...object' or 'database..object').

yes

AllowSinglePartName

Allow usage of object name not being qualified with schema name.

no

AllowTwoPartName

Allow object names using two part name ('schema.object').

yes

AllowThreePartName

Allow object names using three part name ('database.schema.object').

yes

AllowFourPartName

Allow object names using four part name ('server.database.schema.object').

yes

Remarks

The rule does not need Analysis Context or SQL Connection.

Categories

Design Rules

Additional Information

There is no additional info for this rule.

Example Test Script
SQL
 1CREATE TRIGGER dbo.Test_Trigger
 2ON TestTable1
 3FOR UPDATE
 4AS
 5
 6SELECT 
 7       *,Database1..Table4.Column1
 8FROM 
 9     ..Table1,
10     Schema1.Table2, Database1..Table3 /*IGNORE:SA0136(LINE)*/
11     INNER JOIN Database1..Table4   
12        ON Database1..Table4.Column1=Database1..Table3.Column1 AND 
13           Database1..Table4.Column2=Database1.Schema1.Table1.Column2
14     INNER JOIN Database1.Schema2.Table4 ON 
15           Database1..Table2.Column1=Database1..Table3.Column1 AND 
16           Database1..Table2.Column2=Database1.Schema1.Table1.Column2
17WHERE 
18      Database1.Schema1.Table1.Column1=Database1.Schema1.Table2.Column1 AND 
19      Database1.Schema1.Table3.Column1=Database1.Schema1.Table2.Column3
20
21DELETE Database1..Table4 
22FROM 
23     Database1.Schema1.Table1,
24     Database1..Table2, -- IGNORE:SA0136
25     ..Table3
26     INNER JOIN Table4 
27        ON Database1..Table4.Column1=Database1..Table3.Column1 AND 
28           Database1..Table4.Column2=Database1.Schema1.Table1.Column2
29     INNER JOIN Database1.Schema2.Table4 ON 
30           Database1..Table2.Column1=Database1..Table3.Column1 AND 
31           Database1..Table2.Column2=Table1.Column2
32WHERE 
33      Database1.Schema1.Table1.Column1=Database1.Schema1.Table2.Column1 AND 
34      Database1.Schema1.Table3.Column1=Database1.Schema1.Table2.Column3
35
36
37UPDATE Schema1.Table4  SET Database1..Table4.Column5 = 5, Column4 = 4 FROM Database1..Table4 WHERE Database1..Table4.Column7 = 0
38
39INSERT INTO Schema1.Table5 VALUES (1,'2',3,'4')
40
41UPDATE Table4  SET Database1..Table4.Column5 = 5, Column4 = 4 FROM Database1..Table4 WHERE Database1..Table4.Column7 = 0
42
43INSERT INTO Schema1.Table5 VALUES (1,'2',3,'4')
44
45SELECT * 
46FROM
47    BADGE_Category bc
48    left JOIN CHANGEDRIVER..BADGE b  ON b.BADGE_CATEGORY_ID = bc.BADGE_CATEGORY_ID
49    left join DBO.BADGE_EVENT be  on be.BADGE_ID = b.BADGE_id
50    left JOIN CHANGEDRIVER.DBO.[EVENT] e  ON be.EVENT_ID = e.EVENT_ID
51    INNER JOIN CHANGEDRIVER.dbo.CAMPAIGN_BADGE_XREF cbx  ON b.BADGE_ID = cbx.BADGE_id
52WHERE
53    cbx.CAMPAIGN_ID = 1
54ORDER BY bc.BADGE_CATEGORY_ID, b.BADGE_LEVEL
55
56declare @result int 
57EXEC @result = uspGetEmployeeManagers 6;
58EXEC uspGetEmployeeManagers 6;
59EXEC dbo.uspGetEmployeeManagers 6;
60EXEC MyDatabase.dbo.uspGetEmployeeManagers 6;
61EXEC Linked.MyDatabase.dbo.uspGetEmployeeManagers 6;
62
63
64UPDATE Table4Alias  SET Database1..Table4.Column5 = 5, Column4 = 4 FROM Schema1.Table4 a WHERE Database1..Table4.Column7 = 0
65
66SELECT f.CustCode, f.CustLocNum 
67FROM #DimFacility f
68WHERE f.FacilityKey = 1;
69
70SELECT f.CustCode, f.CustLocNum 
71FROM ##DimFacility f
72WHERE f.FacilityKey = 1;
73
74SELECT f.CustCode, f.CustLocNum 
75FROM inserted;
76
77SELECT f.CustCode, f.CustLocNum 
78FROM inserted i;
79
80SELECT f.CustCode, f.CustLocNum 
81FROM table1 inner join deleted d on d.id = table1.id;
82
83delete from dto.table1 
84output deleted.*

Analysis Results

 MessageLineColumn
1SA0136 : Use fully qualified object name.2616
2SA0136 : Use fully qualified object name.417
3SA0136 : Use fully qualified object name.474
4SA0136 : Use fully qualified object name.5715
5SA0136 : Use fully qualified object name.585
6SA0136 : Use fully qualified object name.647
7SA0136 : Use fully qualified object name.815
See Also

Other Resources