Post reply

How to get the SP's based on certain search string

  • August 30, 2012 at 8:38 am

    #393057

    Hi,

    I have 100 Stored Procedure. I want to search for 'Employee' key word in all 100 SP's, but it is not possible for me to go in all 100 SP's and check for the keyword 'Employee'. Is there any Query for the above . Please Help

  • August 30, 2012 at 8:56 am

    #1531951

    SELECT o.name

    FROM sys.sql_modules m

    JOIN sys.objects o ON m.object_id = o.object_id

    WHERE definition like '%Employee%'

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

  • August 30, 2012 at 9:03 am

    #1531959

    Heh. Now this is do-able! 😀

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.     [/font]    [font="Verdana"] "Performance is our middle name."[/font]

  • August 30, 2012 at 9:04 am

    #1531961

    Thanks Luis. That work.

  • August 30, 2012 at 9:17 am

    #1531974

    Someone beet me to it but you can also use:

    SELECT DB_NAME()+'.'+ROUTINE_SCHEMA+'.'+ROUTINE_NAME [Stored Proc]

    FROM INFORMATION_SCHEMA.ROUTINES

    WHERE CHARINDEX('@prmSorted',ROUTINE_DEFINITION)<>0

    AND ROUTINE_TYPE='PROCEDURE'

    "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

    -- Itzik Ben-Gan 2001

  • August 30, 2012 at 9:20 am

    #1531977

    For SQL Server 2000 you should use syscomments table.

    You might not need it, but it's a good thing to now.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

  • August 30, 2012 at 9:33 am

    #1531978

    Use Redgate's free SQL Search tool - no coding required and works well.

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • August 30, 2012 at 10:30 am

    #1532012

    also, if the stored procs are spread accross multiple databases you could use this:

    -- create temp table for result set

    IF OBJECT_ID('tempdb..#sprocSearchResults') IS NOT NULL

    DROP TABLE #sprocSearchResults

    CREATE TABLE #sprocSearchResults (StoredProc varchar(200));

    -- set search string

    DECLARE @searchstr1 varchar(100)='mmo',

    @searchstr2 varchar(100)='task'

    -- search each database for the search string

    DECLARE @sql varchar(1000)=

    '

    USE [?];

    INSERT INTO #sprocSearchResults

    SELECT DB_NAME()+''.''+ROUTINE_SCHEMA+''.''+ROUTINE_NAME [Stored Proc]

    FROM INFORMATION_SCHEMA.ROUTINES '

    SET @sql=@sql+

    'WHERE ( CHARINDEX('''+@searchstr1+''',ROUTINE_DEFINITION)<>0 AND

    CHARINDEX('''+@searchstr2+''',ROUTINE_DEFINITION)<>0 )

    AND ROUTINE_TYPE=''PROCEDURE'';'

    EXEC sp_msForEachDB @sql

    -- Result set

    SELECT StoredProc FROM #sprocSearchResults

    ORDER BY StoredProc

    "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

    -- Itzik Ben-Gan 2001

Viewing 8 posts - 1 through 7 (of 7 total)

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