Sql-server – Identify NON-ANSI joins in SQL Server 2008 R2 on a Database that’s in 2000 (80) compatibility mode

join;sql serversql-server-2008-r2t-sqlupgrade

We have a very old database running on SQL Server 2008 R2. The database is running in 2000 (80) compatibility mode and has oodles of NON-ANSI joins in stored procedures, views and functions.

We need to update this database to at least 2005 compatibility mode to enable the use of CTE SQL, ROW_NUMBER OVER (PARTITION BY) and so on.

When we first upgraded to SQL Server 2008 R2 we obviously ran Upgrade Advisor 2008 R2 first which highlighted all SQL that had non-ansi joins, at the time we just ignored them and put the database in SQL Server 2000 (80) compatibility mode.

Our problem now is that SQL Server 2008 R2 Upgrade advisor will no longer work for us as the version isn’t supported (as we're already using that version), and 2000 (80) databases aren’t supported in the 2012 Upgrade advisor either.

My question, short of scripting the whole database and painstakingly going through it checking for *= and =* and *=* etc, is there a tool we can use to detect all procs, views and functions that contain these joins so we can get them re-written.

Best Answer

This may produce some false positives (e.g. you may have *= in a comment), but should be a good start:

SELECT 
  obj = QUOTENAME(SCHEMA_NAME(o.[object_id]))
      + '.' + QUOTENAME(o.name),
  o.type_desc
FROM sys.sql_modules AS m
INNER JOIN sys.objects AS o
ON m.[object_id] = o.[object_id]
WHERE m.definition LIKE '%=*%'
OR m.definition LIKE '%*=%';
Related Question