Search Stored Procedures for string/text
SQL Code
OK so just like the post below about searching for columns or table names. This post is all about how to search inside Stored Procedures for strings / text.
This can be really useful especially if you know a piece of code is called but your not sure which stored proc calls it.
As before we are going to use the ‘users’ string to keep consistency.
I had searched high and low for this code on the internet but couldn’t find anything. All of the examples I found only searched in the first 255 characters of stored procedure which is OK if its a declaration your looking for however not too good if your stored proc has unions and has declarations half way down the procedure. So I decided to write my own.
Anyway on with the script. (really nice and simple)
– Author : Martin Hall
– Date 17th February 2010
– Search Stored Procedures for Text. (see Definition like)
SELECT Name
FROM sys.procedures
WHERE OBJECT_DEFINITION(object_id) LIKE ‘%users%’
Hope you Enjoy
