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

Introduction

The primary issue is the absence of using fully qualified object names in T-SQL statements, such as SELECT , UPDATE , DELETE , INSERT , and EXECUTE .

Description

In T-SQL code, not specifying fully qualified object names can lead to ambiguity, performance issues, and security risks. Fully qualifying object names means specifying the database , schema , and objectName . This practice ensures clarity and improves the efficiency of query execution.

For example:

-- Without fully qualified name
SELECT * FROM Employees;

-- With fully qualified name
SELECT * FROM HR.Employees;

The first query without a fully qualified name may cause the SQL Server to take extra time to resolve the correct object, leading to potential performance degradation. Specifying the schema (e.g., HR.Employees ) helps avoid such delays and ensures the correct object is being accessed, even when objects with the same name exist in different schemas.

Ambiguity in identifying the correct object, leading to potential runtime errors or incorrect data retrieval.
Performance overhead due to SQL Server’s need to determine the correct object through name resolution.
Security issues, as inadvertent access to incorrect objects may lead to exposure or modification of sensitive data.

How to fix

Ensure that all object names in your T-SQL statements are fully qualified with the schema to avoid ambiguity, enhance performance, and strengthen security.

Follow these steps to address the issue:

1.Identify the T-SQL statements where object names are used without schema qualification. Common statements include SELECT , UPDATE , DELETE , INSERT , and EXECUTE .

2.Modify each unqualified object name by adding the appropriate schema name. This involves using the format schema_name.object_name . Ensure that you know the correct schema for each object.

3.Test the modified queries to ensure they work as expected and that they access the correct objects, especially if multiple objects with similar names exist in different schemas.

For example:

-- Correct the object name by specifying the schema
SELECT * FROM HR.Employees;
-- Change UPDATE statement to use fully qualified object names
UPDATE Sales.Orders
SET OrderStatus = 'Completed'
WHERE OrderID = 12345;

Scope

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

Parameters

Name	Description	Default 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 requires Analysis Context. If context is missing, the rule will be skipped during analysis.

Effort To Fix

3 minutes per issue.

Additional Information

There is no additional info for this rule.

Example Test SQL

EXEC uspGetEmployeeManagers 6;

SELECT * FROM Table1 t1 INNER JOIN Table2 t2 ON t2.t1_id = t1.id

Example Test SQL with Automatic Fix

EXEC [dbo].uspGetEmployeeManagers 6;

SELECT * FROM Table1 t1 INNER JOIN Table2 t2 ON t2.t1_id = t1.id

Analysis Results

	Message	Line	Column
1	SA0136 : Use fully qualified object name.	1	5
2	SA0136 : Use fully qualified object name.	3	14
3	SA0136 : Use fully qualified object name.	3	35

Analysis Rules