How to format a SQL query to qualify object names explicitly

ApexSQL Refactor is a SQL Server Management Studio and Visual Studio add-in, which formats SQL query and refactors SQL code using 11 code refactors and more than 200 formatting options.

The Object name qualifying feature allows users to refactor a SQL script so that all object names are qualified. The feature can refactor object names by qualifying these in a way to add:

• The owner (schema/user) of objects to each object listed in SQL code in the format owner.object. Schemas/users are added to objects if the object does not contain these, if it can have owners added (for example, assemblies cannot have schemas), and if it exists in a database
• The object name (table, view name, etc.) to column names in the format table.column. Object names are added to column names if the column name exists in the referred object and the column name is not already qualified by the object name
• The alias name to column names in the format alias.column. Alias names are added to column names if the alias is used in the FROM clause for the table, and if the column does not already contain the alias name

By using the Qualify object name feature to qualify the object when executing a SQL query, server will not check if the current user is the owner of the object, meaning that no additional work will be required from SQL server. This speeds up the query execution.

Object names enclosed in square brackets will be qualified as well and the Qualify object name feature will preserve all manually entered SQL server or SQL database names intact.

To qualify all objects and column names, open the SQL script in SQL Server Management Studio or Visual Studio. In the ApexSQL menu, select the ApexSQL Refactor menu, click the Qualify object name command, and let the add-in to complete the refactoring of SQL script with a single click:

The following are examples to illustrate this:

In this example, the schema will be added to object names in a SQL query. The ProductCategory and Product tables are without the Object qualifier:

SELECT * FROM ProductCategory tab1
JOIN Product tab2
ON tab2.ProductSubcategoryID = tab1.ProductCategoryID
WHERE tab1.Name LIKE 'B%'

After refactoring SQL query:

SELECT * FROM Production.ProductCategory tab1
JOIN Production.Product tab2
ON tab2.ProductSubcategoryID = tab1.ProductCategoryID
WHERE tab1.Name LIKE 'B%'

An example of adding an SQL object name to a column:

SELECT Name FROM HumanResources.Department
WHERE Name LIKE '%'

After refactoring SQL query column “Name” will be qualified by adding the SQL object name:

SELECT Department.Name FROM HumanResources.Department
WHERE Department.Name LIKE '%'

An example of adding the alias name to a column name:

SELECT Name FROM HumanResources.Department t1
WHERE Name LIKE '%'

After refactoring SQL query column “Name” will be qualified by adding the SQL object name:

SELECT t1.Name FROM HumanResources.Department t1
WHERE t1.Name LIKE '%'

All the changes made by using the Qualify object name feature, can be undo using the standard SSMS Undo command.

This add-in for SQL Server Management Studio and Visual Studio takes the pain out, as it can format SQL query to make it more explicit.

Useful resources:

How to: Fully qualify the names of database objects
Rename all references to a database object
Using identifiers as object names