Recently, I come to an issue which need to be fix in some sored procedure. I need to modify field.This was used in multiple stored procedure.I was needed to search for all the Stored Procedures having a given text in its definition. So, I searched and found most of the top results returned were suggesting to use INFORMATION_SCHEMA.ROUTINES view like below, which is not going to return correct result in all the scenario's.
So I write a script as below and fixed the issue.
SELECT OBJECT_NAME(object_id), OBJECT_DEFINITION(object_id)
WHERE OBJECT_DEFINITION(object_id) LIKE '%@ApplicationId%'