Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
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 '13 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 '13 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 '13 at 15:45
add comment

2 Answers

up vote 3 down vote accepted

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 '%*=%';
share|improve this answer
    
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 '13 at 15:42
add comment

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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