Transact SQL – Non-ANSI Joins

Background

Trying to see which SQL modules uses legacy Non-ANSI SQL Joins.

Discovery

There are a couple of pathways we can use to find sql code that relies on Non-Ansi SQL Joins.

The mediums we will use are :-

  1. Code
    • Execute Code
  2. Dynamic Management Views
    • System Catalog Views
      • sys.sql_Modules
  3. SQL Server Profiler

Code

Code Execution

SQL

SQL
DECLARE	@return_value int

EXEC	@return_value = [dbo].[sp_get_bottom2]

SELECT	'Return Value' = @return_value

GO
Output
Output – Text
Msg 102, Level 15, State 1, Procedure dbo.sp_get_bottom2, Line 8 [Batch Start Line 2]
Incorrect syntax near '*='.

Msg 102, Level 15, State 1, Procedure dbo.sp_get_bottom2, Line 17 [Batch Start Line 2]
Incorrect syntax near '*='.

Msg 102, Level 15, State 1, Procedure dbo.sp_get_bottom2, Line 26 [Batch Start Line 2]
Incorrect syntax near '*='.

Explanation
  1. Msg 102
    • Msg 102, Level 15, State 1, Procedure dbo.sp_get_bottom2, Line 8 [Batch Start Line 2]
      Incorrect syntax near ‘*=’.

Dynamic Management Views

System Catalog Views

sys.sql_modules

SQL

select *

from   sys.sql_modules tblSSM

where
        (
               (tblSSM.definition like '%*=%' )
            or (tblSSM.definition like '%=*%' )
        )
Output

NonAnsiJoins.20190221.0755AM

SQL Server Profiler

Trace Definition

Outline

  1. Deprecation
    • Deprecation Announcement
    • Deprecation Final Support

Image

sqlServerProfiler.traceProperties.eventsSelection.20190221.0800AM.PNG

Trace Events

Image

sqlServerProfiler.traceEvents.01.traceEvents.20190221.0804AM.PNG

Explanation

  1. Found
    • Deprecation Final Support