Freigeben über

Query for a list of Procs that contain a string

  • Artikel

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.

Comments

  • Anonymous
    July 23, 2004
    I've been taught that it's better to use the information_schema views rather than hitting sysobjects, since sysobjects is subject to change.

    select * from information_schema.routines
    where routine_definition like '%' + @StringToMatch + '%'
    --and specific_name not like 'sp_%'
  • Anonymous
    July 23, 2004
    Thanks for the comment Jon. The post has been updated!
  • Anonymous
    July 23, 2004
    this one is better, IMHO
    and faster:

    http://gensystem.europe.webmatrixhosting.net/al/archive/2004/06/22/416.aspx