Well last Christmas I was very happy, because we ceased support for SQL Server 2000.

I could stop twisting my brain and use friendly analytical functions. (Believe me, when it comes to migrate stored procedures from SQL-Server to Oracle Analytical functions are one of your best friends).

Later we discovered that we overlooked a tiny annoyance.

When customers replaced there old machines by new ones, by default their compatibility level is set according to the old machine and problems are postponed until later.

Which problem? Analytical functions work in SQL Server 2005 despite compatibility level 80.

I'm afraid to tell that other new features like PIVOT is inhibited.

Now it is good time to try to get rid of cmptlevel 80, but how?

For me it is mostly scanning the procedures, views, functions and triggers for old style outer joins like '*=' and '=*'.

First let us find the defining text from the system tables. Initially it was saved in small chunks in syscomments. Hhm how to deal with the case when '*' and '=' are split onto to succeeding chunks.

Next don't waste your time with Select ROUTINES from INFORMATION_SCHEMA.ROUTINES it truncates the definition to 4000 characters.

Finally this is the code to query for the problematic objects:

select o.name , o.type, datalength(definition) len, len(definition) len, definition from sys.sql_modules m 
join sysobjects o on o.id = m.object_id
where (definition like '%*=%' or definition like '%=*%') 

But there is a further hurdle. Because default is derived from default the default width for text results is too small ( I think it is 256, but as it is the first thing I change to 8192 in SSMS, I wont bet).

Any case this helps to identify the rows which need deeper examination.

Perhaps sp_help displays the full definition for all possible cases.

Perhaps it does, I don't know for sure, because I got tired of that problems created by punchcard heads. Normally I use ADO and PowerShell, but they may not be installed on the customers machine.

Are there any 100% waterproof T-SQL scripts to do the job of displaying or exporting the full definition of procedures found by some condition?


A quick search of StackOverflow would show you use sys.sql_modules or OBJECT_DEFINITION to search for code on SQL Server 2005+. You can simply restore onto SQL Server 2005+ but not run to allow this.

That's if you want fool proof T-SQL. You could concatenate the syscomments rows together if you wish though in SQL Server 2000. I wouldn't personally.

Alternatively, use the SQL Server 2008 upgrade advisor to pick out most (maybe all) possible issues (such as ambiguous ORDER BY clauses which caught me out).

Note the Red Gate SQL Search sensibly only supports SQL Server 2005+

  • My problem is the display of the result. QueryAnalyzer and SSMS have bad habits of truncating the result. BTW the offending code must be fund. Artefacts ignored -- keep *= as comment to make work harder and the rest must manually fixed. – bernd_k Oct 24 '11 at 18:28
  • @bernd_k: hard to spot the real question when you mention all the wrong ways of searching. Is it an SSMS display issue? Or how to search correctly? It doesn't matter what SSMS says if you don't do it properly (upgrade advisor). All you need is the name of the code: then you search when opening to edit. – gbn Oct 24 '11 at 18:33
  • I always felt that replacing the server is the only right moment to fix that problems. Are you saying that there is a tool that would have helped? – bernd_k Oct 24 '11 at 18:35
  • @bernd_k: yes, there is a tool that will pick out most if not all issues. I doubt ANSI-89 joins are your only problem. – gbn Oct 24 '11 at 18:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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