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

Question

I am migrating legacy SQLS2k to 2008R2, and it seems that all the data was executed through stored procs, and any user queries use the legacy external connection syntax *= =* . There are more than a hundred processes, so I don’t want to open them each separately to see if it uses this syntax (most will not), is there a way I can request metadata for the list of procs / functions / views / triggers, then loop finding strings *= or =* by printing the name of the violating object?

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

thank!

Answer 1

Free Search in Red Gate SQL?

Or request sys.sql_modules

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

Note. The views and syscomments of INFORMATION_SCHEMA crop definition are so unreliable.

Answer 2

The problem with using queries is that they do not work if the stored procedure is encrypted, if you are not using the type of DAC connection.

It uses a third-party tool because they help you do this without much hassle. I use ApexSQL Search for free, but I think you cannot go wrong with Red Gate or any other tool there.