Search All Stored Procedures in a Database

  • George Damien Varkey

    SSC-Addicted

    Points: 440

    Comments posted to this topic are about the item Search All Stored Procedures in a Database

  • nigel.

    SSChampion

    Points: 11580

    Unfortunately this doesn't quite work, see the explanation in the following article:

    http://www.sqlservercentral.com/articles/Stored+Procedure/62975/

  • hila

    SSC Enthusiast

    Points: 123

    I use the following piece of code for a while now and with great success:

    SELECT ROUTINE_NAME, ROUTINE_DEFINITION ROUTINE_TYPE FROM INFORMATION_SCHEMA.ROUTINES

    WHERE ROUTINE_DEFINITION LIKE '%text%' AND ROUTINE_TYPE = 'PROCEDURE'

    http://databases.aspfaq.com/database/how-do-i-find-a-stored-procedure-containing-text.html

  • nigel.

    SSChampion

    Points: 11580

    This still suffers from the same problem, in that the ROUTINE_DEFINITION column is only 4000 characters long. Any stored proc longer than 4000 characters in length is split over two rows and any text that spans this 'split' in the definition will not be found by your method. See the link posted earlier.

  • nigel.

    SSChampion

    Points: 11580

    A slight correction :). Having looked at this further it seems that the INFORMATION_SCHEMA.ROUTINES view does not split the procedure definition over more than 1 row it in fact only shows the first 4000 characters of any stored proc. Which actually makes using this view for searching the procedure definitinion less than useful.

    Oh, and the INFORMATION_SCHEMA views are only in 2005 and above.

    For 2005 and above the take a look at the OBJECT_DEFINITION function, mentinoed in the following article on the same subject:

    http://databases.aspfaq.com/database/how-do-i-find-a-stored-procedure-containing-text.html

  • Abhijit More

    SSCertifiable

    Points: 6897

    Assuming this post belongs to SQL server 2005, you can use the below query to return all SP in the database

    SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_TYPE = 'PROCEDURE'

    Abhijit - http://abhijitmore.wordpress.com

  • ken.trock

    SSCertifiable

    Points: 5147

    I added a quick option to George's original block to specify the DB you want to search. This way it can sit in Master and be called from anywhere on the server.

    But this split at character 4000 in syscomments is an eye opener. Means this solution isn't complete, even for searching procs only.

    Ken

    ALTER proc [dbo].[SearchObjects]

    @database varchar(100),

    @searchString varchar(100)

    As

    Declare @Sql nvarchar(1000)

    Set @Sql = 'SELECT Distinct SO.Name FROM ' + @database + '.dbo.' + 'sysobjects SO (NOLOCK) ' +

    'INNER JOIN ' + @database + '.dbo.' + 'syscomments SC (NOLOCK) on SO.Id = SC.ID ' +

    'AND SO.Type = ''P'' AND SC.Text LIKE ''%' + @searchString + '%'' ORDER BY SO.Name'

    Exec sp_executesql @Sql

  • Sri Kondeti-293259

    SSC Journeyman

    Points: 77

    One suggestion is to add the upper to both side of the comparision to make it work with case senstive databases.

    Ex: where upper(text) like upper(@searchstring)

  • akljfhnlaflkj

    SSC Guru

    Points: 76202

    Thanks for the script.

Viewing 9 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply