Results 1 to 3 of 3
  1. #1
    Join Date
    Jun 2007
    Location
    London
    Posts
    117

    Unanswered: T-SQL and Stored Procedures

    Hello all,

    [Not sure if this question belongs here]

    I have seen many articles that explain how to search for text in stored procedures, but being a noob at T-SQL, I need some help.

    What I do now is use this query "exec sp_depends table_name" and I look through all the stored procedures manually! I have around 1000 tables with 100s of stored procedures! So How can I use this:

    exec sp_depends a_table_name
    with this:

    Code:
    CREATE PROCEDURE Find_Text_In_SP
    @StringToSearch varchar(100)
    AS
       SET @StringToSearch = '%' +@StringToSearch + '%'
       SELECT Distinct SO.Name
       FROM sysobjects SO (NOLOCK)
       INNER JOIN syscomments SC (NOLOCK) on SO.Id = SC.ID
       AND SO.Type = 'P'
       AND SC.Text LIKE @stringtosearch
       ORDER BY SO.Name
    GO
    http://www.knowdotnet.com/articles/storedprocfinds.html

    Thank you for any help

  2. #2
    Join Date
    Nov 2003
    Location
    London
    Posts
    169
    Hi,

    Just use this:



    SELECT Distinct SO.Name
    FROM sysobjects SO (NOLOCK)
    INNER JOIN syscomments SC (NOLOCK) on SO.Id = SC.ID
    AND SO.Type = 'P'
    where SC.Text LIKE '% %'
    ORDER BY SO.Name

    and put the text you are looking for between the percentage symbols, like so:

    where SC.Text LIKE '%customers%'

  3. #3
    Join Date
    Jun 2007
    Location
    London
    Posts
    117
    Thank you for your reply.

    Ok I am a noob, so I have to ask:

    Is the text i want to search all I have to change in your code? Do i put my table name as "SO"?

    By the way, I use Sybase query analyzer?!?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •