Query for a list of Procs that contain a string

  • Article

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:

https://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.