How to find all the Stored Procedures having a given text in it?
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:
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):
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.
Wow, that’s crazy that ROUTINE_DEFINITION only gets the first 4000 characters! Any idea as to why they would implement it so? Pretty much everyone (including myself which I believe was one of your sources: http://devblog.grinn.net/2008/02/search-for-stored-procedure-containing.html) uses ROUTINE_DEFINITION to perform such a query, so this is a big find, Basavaraj!
Do you know view sys.Sql_modules?
Select object_name(object_id), definition
From sys.sql_modules
Where definition like ‘%searchstring%’ and objectpropertyex(object_id, ‘isProcedure’)=1
Yes. We can use sys.Sql_modules as well.
Brilliant find dude! I was beating my head against my monitor for an hour wondering why I couldn’t match a string in a long SP using ROUTINE_DEFINITION, but now I know! You rock!
Thank you Zach for your comments
I have run across the 4000 character limit also. I believe it just the way Enterprise Manager displays the results. The entire text of the object is there, just not displayed.
To prove it try the following:
Declare @sql nvarchar(MAX)
SELECT @sql = ROUTINE_DEFINITION(OBJECT_ID)
FROM INFORMATION_SCHEMA.ROUTINES
WHERE [NAME] = ‘your test routine name here’
Print @sql
This should show the entire text of the command.
The best option is:
SELECT *, OBJECT_DEFINITION(object_id) ObjectDefinition
get everything
FROM sys.objects
WHERE OBJECT_DEFINITION(object_id) LIKE ‘%SearchString%’