Loading ...

Search a text in all the stored procedures | CodeAsp.Net

Search a text in all the stored procedures

 /5
0 (0votes)

Today I was having a requirement where I need to search a text in all the stored procedures in my database. Basically I was in a need to get the list of all those stored procedures which contains a particular text. So I decided to blog for it. Below is the script for it:

SELECT  [ROUTINE_SCHEMA] ,
        [ROUTINE_NAME] AS [PROCEDURE NAME] ,
        [ROUTINE_DEFINITION]
FROM    [INFORMATION_SCHEMA].[ROUTINES]
WHERE   [ROUTINE_DEFINITION] LIKE '%searchtext%'
        AND [ROUTINE_TYPE] = 'PROCEDURE'
ORDER BY [ROUTINE_SCHEMA] ,
        [ROUTINE_NAME]

 

I have attached the screenshot below for the results which I was getting when I run the above query with "add" as the search text on tempdb database.

EDIT: Information_Schema has a limit of 4000 charcters so you can use the following query then:

SELECT  o.type_desc AS ROUTINE_TYPE ,
        o.[name] AS ROUTINE_NAME ,
        m.definition AS ROUTINE_DEFINITION
FROM    sys.sql_modules AS m
        INNER JOIN sys.objects AS o ON m.object_id = o.object_id
WHERE   m.definition LIKE '%searchtext%' 

Comments (2)

   
mohit
Nice Blog Sir.
5/13/2010
 · 
by
   
hajan
Nice tip! I do personally really like working with the db schema objects and everything in the master db - it (somehow) makes me feel having control over everything.
5/20/2010
 · 
by
  • :*
  • :*
  • :
 *

Top Posts