13

I'm migrating a legacy SQLS2k to 2008R2, and it seems all data access was done through stored procs, and any custom queries use the legacy *= =* outer join syntax. There are upwards of a hundred procs so I don't want to open each one individually to see if it uses that syntax (most wouldn't), is there a way I can query the metadata for a list of procs/functions/views/triggers, then loop through searching for the *= or =* strings, printing out the name of the offending object?

My background is oracle, I know how to find the metadata views there, but I'm a bit new to Sql Server. Downgrading the compatibility version is not an option.

thanks!

Damien_The_Unbeliever
  • 234,701
  • 27
  • 340
  • 448
matao
  • 636
  • 3
  • 14
  • 22
  • Glad you are fixing this, that code should never have been used even in SQl sserver 2000 as it is unreliable and doesn't always do an outer join, sometimes it does a cross join. Hopefully at the same time you are removing all other implicit joins in those queries as mixing implicit and explict joins can cause issues as well. Implicit joins are a SQL antipattern anyway and should not be used in any database. See link for examples:http://stackoverflow.com/questions/1080097/the-multi-part-identifier-could-not-be-bound-on-sql-server-2008/1080407#1080407 – HLGEM Sep 02 '11 at 17:28

2 Answers2

29

Free Red Gate SQL Search?

Or query sys.sql_modules

SELECT OBJECT_NAME(object_id)
FROM sys.sql_modules
WHERE definition LIKE '%=*%' OR definition LIKE '%*=%'

Note: INFORMATION_SCHEMA views and syscomments truncate the definition so are unreliable.

Matt Sach
  • 1,162
  • 16
  • 37
gbn
  • 422,506
  • 82
  • 585
  • 676
  • +1 for Red-Gate SQL Search - absolutely indispensable and *FREE* tool for DB programmers (and DBA's) ! – marc_s Jun 15 '11 at 07:54
  • perfect! Accepted :) Only trouble is that it tells me all my Journal triggers use the old syntax... FML! – matao Jun 15 '11 at 08:02
  • also, just installed that Red Gate tool, it's pretty awesome, thanks! – matao Jun 15 '11 at 08:11
2

Problem with using queries is that these don’t work if stored procedure is encrypted unless you’re running DAC connection type.

This is where third party tool come in handy because they help you do this without too much hassle. I’m using ApexSQL Search that’s free but I guess you can’t go wrong with Red Gate or any other tool out there.

Igor Voplov
  • 973
  • 9
  • 7