EX0019 : Find references to non-existing objects and columns
Introduction
Section titled “Introduction”The rule identifies references to non-existing objects or columns in SQL queries.
Description
Section titled “Description”Referencing objects or columns that do not exist can lead to runtime errors, hinder query performance, and disrupt database operations. This problem often occurs due to typos, outdated schema information, or incomplete migrations.
Example of a query with a reference to a non-existing column:
SELECT NonExistingColumn FROM TableName;The query above is problematic because NonExistingColumn does not exist in TableName . Attempting to execute this query will result in an error, halting the process and potentially impacting application stability.
-
A runtime error can occur when attempting to access a non-existing object, causing disruptions in application workflows.
-
Frequent schema changes without corresponding updates to queries can lead to maintenance issues and difficult-to-trace bugs.
How to fix
Section titled “How to fix”The rule has a Batch scope and is applied only on the SQL script.
Parameters
Section titled “Parameters”| Name | Description | Default Value |
|---|---|---|
| IgnoreTemporaryTables | The parameter specifies if to ignore temporary tables not created in current batch. | yes |
Remarks
Section titled “Remarks”The rule requires Analysis Context. If context is missing, the rule will be skipped during analysis.
Effort To Fix
Section titled “Effort To Fix”20 minutes per issue.
Categories
Section titled “Categories”Explicit Rules
Additional Information
Section titled “Additional Information”There is no additional info for this rule.
Example Test SQL
Section titled “Example Test SQL”SELECT 'Total income is', ((OrderQty * Price) * (1.0 - UnitPriceDiscount)), ' for ',p.Name AS ProductNameFROM Production.Product AS pINNER JOIN Sales.SalesOrderDetail AS sodON p.ProductID = sod.ProductIDORDER BY ProductName ASC;
SELECT p.ProductName AS ProductName,NonDiscountSales = (OrderQty * UnitPrice),Discounts = ((OrderQty * UnitPrice) * UnitPriceDiscount)FROM Production.Product AS pINNER JOIN Sales.SalesOrderDetail AS sodON p.ProductID = sod.ProductIDORDER BY ProductName DESC;GOAnalysis Results
Section titled “Analysis Results”| Message | Line | Column | |
|---|---|---|---|
| 1 | EX0019 : Invalid column reference Price. | 1 | 39 |