4

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.

Improve this question
3
  • 2
    Did you know that even in SQL Server 2000, you should not use these joins as they often give the wrong answer. Somtimes they can get interpreted as cross joins instead of outer joins for instance. When you convert I would carefully check to ensure that your new version gives the same results as the old version. You may not have been doing the join you thought you were doing.
    – HLGEM
    Feb 12, 2013 at 15:13
  • @HLGEM We did know that yes, the database in question was written by a 3rd party and has been used for 20 years - We're only scoping what needs changing at the point, all the changes will be done one by one and result sets compared before committing - going to be a VERY tedious few weeks.
    – HeavenCore
    Feb 12, 2013 at 15:40
  • 1
    I just wanted to be sure you knew that waht the query was actaully doing might not be what you were expecting it to do. But yes you are in for a tedious few weeks.
    – HLGEM
    Feb 12, 2013 at 15:45

2 Answers 2

Reset to default
4

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 '%*=%';
Improve this answer
1
  • Super - I was going to script the entire schema to an old SQL 2005 box and run upgrade adviser against that, but your answer seems to give better results! Cheers.
    – HeavenCore
    Feb 12, 2013 at 15:42
0

The syscomments compatibility view contains a copy of the code for all stored procedures, triggers, functions and similar objects. You can easily search it using "LIKE" criteria to find a list of object IDs that need to be updated.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.