sql-serversql-server-2008joinsql-server-2000outer-join

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


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!


Solution

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