Search Stored Procedures for string/text

Posted by admin on Mar 30, 2010 in code, SQL, tips |
SQL Code

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

Reply

Copyright © 2012 The Test Manager Blog All rights reserved. Theme by Laptop Geek.