Recently, I was needed to search for all the Stored Procedures having a given text in its definition. So, as usual I did the Googling, and most of the top results returned were suggesting to use the
INFORMATION_SCHEMA.ROUTINES view like below, which is not going to return the correct result in all scenarios:
SELECT ROUTINE_NAME, ROUTINE_DEFINITION
WHERE ROUTINE_DEFINITION LIKE '%SearchString%'
As usual, I tried to understand the query and thought of checking its 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 the view, the
ROUTINE_DEFINITION column is defined as:
So with this, it is clear that it will not return all the Stored Procedures which have the first occurrence of the search sting in its definition after 4000 characters.
To get the correct results, we can use the
sys.procedures view as below, as the return type of the function
OBJECT_DEFINITION(object_id) which returns the Stored Procedure definition of type
SELECT OBJECT_NAME(object_id), OBJECT_DEFINITION(object_id)
WHERE OBJECT_DEFINITION(object_id) LIKE '%SearchString%'
There are multiple alternative ways with which we can correctly find all the Stored Procedures 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. Visit my blog: SqlHints.com for many more such articles on SQL Server.