This article describes the benefits of using the ApexSQL Refactor option for qualifying object names.
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.
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:
- From the ApexSQL Refactor menu select the Qualify object names command:
- 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;