Results 1 to 13 of 13
  1. #1
    Join Date
    Feb 2010
    Posts
    75

    Unanswered: Search Stored Procedure Text

    Ive been using the following code to search the bodies of all my stored procedures for a keyword:

    Code:
    SELECT ROUTINE_NAME, ROUTINE_DEFINITION 
        FROM INFORMATION_SCHEMA.ROUTINES 
        WHERE ROUTINE_DEFINITION LIKE '%foobar%' 
        AND ROUTINE_TYPE='PROCEDURE'
    Its an extremely handy snippet, especially if you for example change a column name and need to find all the code that references the column.

    However I was horrified to find out that it doesnt always work!!

    I searched for a keyword LIKE '%PERSON_ID%' and it returned about 8 results, but it missed at least one stored procedure! I can open up the offending stored procedure and find the text in there plain as day. Why isnt it being returned when I do a search? This is not good! I rely on that code snippet to tell me where I need to do updates.
    This is pretty urgent! Thanks!

  2. #2
    Join Date
    Feb 2010
    Posts
    75
    Nevermind, answered my own question.

    My little snippet only searches the first 4000 chars of the procedure.

    Anyone have a better way to search Stored Procedure code?

  3. #3
    Join Date
    Jan 2003
    Location
    British Columbia
    Posts
    44
    The Information Schema Views query the system tables.

    Open the Routines view and find out where the data for the Routine_Definition column is stored (most likely the SysComments table). Then query that table directly.

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    If this is SQL 2005+ search the sys.sql_modules table, definition column.

  5. #5
    Join Date
    Aug 2004
    Location
    Dallas, Texas
    Posts
    831
    If sql 2k then search syscomments?

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    definition in sys.sql_modules is NVARCHAR(MAX). The text[I think that's the name??] column in syscomments is NVARCHAR(4000) so will probably result in the same problem. I assume, anyway, that the name of this table is getting split between rows because it falls near a character number multiple of 4000.

  7. #7
    Join Date
    Aug 2004
    Location
    Dallas, Texas
    Posts
    831
    Quote Originally Posted by pootle flump View Post
    definition in sys.sql_modules is NVARCHAR(MAX). The text[I think that's the name??] column in syscomments is NVARCHAR(4000) so will probably result in the same problem. I assume, anyway, that the name of this table is getting split between rows because it falls near a character number multiple of 4000.
    I don't believe sql_modules exist in sql 2k. I generally use:

    select o.name
    from sysobjects o
    inner join syscomments c on o.id = c.id
    where c.text like '%something%'

    P.S. You will have 1:m rows in syscomments, I believe.

  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    That's correct it isn't in there.

    My point is if he is 2k5+ then sql_modules is superior to syscomments. You are right - if he is 2k then he cannot use sql_modules.

    The problem with syscomments is that if "something" starts (for example) at the 3998th character then the query you posted will not work. The end of the text column in the first row will be "so" and the start of the text column for the next row will be "mething". The solution to get round this (IIRC) is a rather convoluted process of BCPing the text column of syscomments into a file and searching using a text editor.

  9. #9
    Join Date
    Aug 2004
    Location
    Dallas, Texas
    Posts
    831
    Quote Originally Posted by pootle flump View Post
    The problem with syscomments is that if "something" starts (for example) at the 3998th character then the query you posted will not work. The end of the text column in the first row will be "so" and the start of the text column for the next row will be "mething". The solution to get round this (IIRC) is a rather convoluted process of BCPing the text column of syscomments into a file and searching using a text editor.
    You are correct. The below does not return something.

    Code:
    declare @text table (row_id int ,text_str nvarchar(4000))
    
    insert into @text
    select 1,
    space(3998) + 'So'
    union
    select 2,
    'mething'
    
    select *
    from @text
    where text_str like '%something%'

  10. #10
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    I guess you could use sp_helptext to extract SP definitions in SQL Server prior to 2008. In the latter version there is the object_definition() function.

  11. #11
    Join Date
    Aug 2004
    Location
    Dallas, Texas
    Posts
    831
    Quote Originally Posted by n_i View Post
    I guess you could use sp_helptext to extract SP definitions in SQL Server prior to 2008. In the latter version there is the object_definition() function.
    Still run into the 4k line limit and the next line might contain remainder of search criteria.

  12. #12
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    What line? Doesn't sp_helptext return the complete object definition?

  13. #13
    Join Date
    Aug 2004
    Location
    Dallas, Texas
    Posts
    831
    Read post #8, :-)))

Posting Permissions

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