Query for a list of Procs that contain a string
I was browsing the SqlServerCentral.com forums today and came across a post where a user was asking how to write a query to list all the stored procedures that contain a specific string. This might be useful, so I thought I better blog it lest I forget.
CREATE PROCEDURE uspGetProcs (@StringToMatch AS VARCHAR(256)) AS
SELECT routine_name
FROM INFORMATION_SCHEMA.ROUTINES
WHERE routine_definition
LIKE '%' + @StringToMatch + '%'
ORDER BY routine_name
The original post can be found here:
http://www.sqlservercentral.com/forums/shwmessage.aspx?messageid=127531
Note: Jon Galloway does point out a great point: Use INFORMATION_SCHEMA rather than the sys* objects as they are subject to change. I edited this post to reflect the more appropriate means of generating the same result. If you want to see the original, click on the link. :)
j.
