how to search Sql Server 2008 R2 stored procedures for a string?

Question

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!

Annikaanniken

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:#1080597 – Mcelroy

Answer 1

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.

Mobster

+1 for Red-Gate SQL Search - absolutely indispensable and FREE tool for DB programmers (and DBA's) ! – Katelynnkaterina

perfect! Accepted :) Only trouble is that it tells me all my Journal triggers use the old syntax... FML! – Annikaanniken

also, just installed that Red Gate tool, it's pretty awesome, thanks! – Annikaanniken

Answer 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.

Seddon