Click here to Skip to main content
12,245,287 members (46,088 online)
Click here to Skip to main content
Add your own
alternative version

Tagged as

Stats

67.1K views
19 bookmarked
Posted

Find all Stored Procedures having a given text in it

, 5 Jun 2013 CPOL
Rate this:
Please Sign up or sign in to vote.
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 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
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_DEFINITION LIKE '%SearchString%'
AND ROUTINE_TYPE='PROCEDURE'

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:

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 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 nvarchar(max):

SELECT OBJECT_NAME(object_id), OBJECT_DEFINITION(object_id)
FROM sys.procedures
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.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Basavaraj P Biradar
Technical Lead http://SqlHints.com
India India
http://SqlHints.com/about/

You may also be interested in...

Comments and Discussions

 
AnswerTy! Pin
Stefan Karlsson4-Sep-13 5:59
memberStefan Karlsson4-Sep-13 5:59 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web02 | 2.8.160426.1 | Last Updated 5 Jun 2013
Article Copyright 2011 by Basavaraj P Biradar
Everything else Copyright © CodeProject, 1999-2016
Layout: fixed | fluid