Recently, I was needed to search for all the Stored Procedures having a given text in its definition. So, as usual did the g 🙂 🙂 gling,  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:

Below Script gives Wrong Result:

SELECT ROUTINE_NAME, ROUTINE_DEFINITION
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_DEFINITION LIKE '%SearchString%'
AND ROUTINE_TYPE='PROCEDURE'

As usual tried to understand the query and thought of checking it’s correctness. And to my surprise when I checked this view’s definition using: sp_helptext ‘INFORMATION_SCHEMA.ROUTINES’,  the ROUTINE_DEFINITION column was returning only the first 4000 characters of the Stored Procedure definition  i.e. in view ROUTINE_DEFINITION column is defined as: convert(nvarchar(4000),object_definition(o.object_id))AS ROUTINE_DEFINITION. So with this it is clear that it will not return all the Stored Procedure’s which have the first occurrence of the search sting in its definition after 4000 characters.

 To get correct results we can use sys.procedures view as below, as the return type of the function OBJECT_DEFINITION(object_id) which returns Stored Procedure definition is of type nvarchar(max):

Below script gives Correct Result:

SELECT OBJECT_NAME(object_id), 
       OBJECT_DEFINITION(object_id)
FROM sys.procedures
WHERE OBJECT_DEFINITION(object_id) LIKE '%SearchString%'

Below screen shot illustrates the difference between these two queries:

There are multiple alternative way’s with which we can correctly find all the Stored Procedure’s having a given text. And sys.procedures explained in this article is one such solution.

Please correct me, if my understanding is wrong. Comments are always welcome.