Avoid implicit name resolution of objects in queries by qualifying object names

Applies to
ApexSQL Refactor

Summary
This article describes the benefits of using the ApexSQL Refactor option for qualifying object names.

Description

SQL Server allows multiple objects with the same name under separate schemas. For example, table Employee can exist under dbo, Person and HumanResources schemas in one database.

Assuming that users default schema is the dbo schema running the following SELECT statement without explicitly qualifying object name will not results with an error:

SELECT * 
FROM Employee

This is because when executing a statement without the schema qualifier SQL Server first looks for an object in the user’s default schema, in this case the dbo schema, and if an object is found SQL Server returns results.

However, in case when an object exists only in the e.g. Person and HumanResources schemas, and not in the user’s default schema or the dbo schema, running the previous SELECT statement without explicitly qualifying object name results with an error:

SELECT * 
FROM Employee

Msg 208, Level 16, State 1, Line 1
Invalid object name ‘Employee’.

This is because the table was in a schema other than users default schema or dbo and in this case SQL Server will stop searching for an object.

Quick tip: An object (a view or a function) created with the SCHEMABINDING option doesn’t allow implicit naming conventions

The following statement will fail to execute:

CREATE VIEW vwJobs
    WITH SCHEMABINDING
AS 
SELECT JobCandidateID
     FROM JobCandidates;

Msg 208, Level 16, State 1, Procedure vwJobs, Line 5
Invalid object name ‘JobCandidates’.

The statement with the properly qualified object will execute successfully:

CREATE VIEW vwJobs
    WITH SCHEMABINDING
AS 
SELECT JobCandidateID
     FROM HumanResources.JobCandidates;

There are two ways to qualify object names in ApexSQL Refactor:

Open a SQL script in SQL Server Management Studio and select part of the script that needs to be formatted, otherwise the entire script in the query window will be formatted:

1. From the ApexSQL Refactor menu select the Qualify object names command:
   

2. Use the Ctrl + Shift + Alt + Q shortcut

ApexSQL Refactor qualifies object names in the following format: table.column and schema.object. For example:

SELECT FirstName
FROM EmailsCustomers
WHERE EmailID = 23;

Using the Qualify object names feature objects and columns are qualified as:

SELECT EmailsCustomers.FirstName
FROM dbo.EmailsCustomers
WHERE EmailsCustomers.EmailID = 23;