Results 1 to 12 of 12
  1. #1
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941

    Unanswered: Search for a phrase in sprocs

    Due to a business rule change, I had to take what was 1 column in a table and split it off into a new table. Now I need to find every time that column is used in a SPROC and change those sprocs. Is there a way to sift through the sprocs to search for a "phrase" (the column name) -- other than reading through every one manually?

    Thanks
    Mark
    Inspiration Through Fermentation

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Not sure about this - would love to know the answer!
    My first guess would be to search through the syscomments
    HTH
    George
    Home | Blog

  3. #3
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    One simple method that I've used in the past....

    Delete the old column on Friday afternoon before I leave, and then come in Saturday AM to see what crashed overnight. That catches about 80% of them.
    Inspiration Through Fermentation

  4. #4
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    syscomments works. also you could script out all the objects and then search the files with your favorite code editor.

    here's a console app I wrote that you can use to script out all objects: http://www.codeplex.com/scriptdb

  5. #5
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Scream testing is "always a good method!"
    Personally I'd rename the column rather than deleting it - but hey - whatever floats your boat!
    George
    Home | Blog

  6. #6
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Code:
    SELECT	*
    FROM	syscomments
    WHERE	id IN (SELECT id FROM sysobjects WHERE type = 'p')
    AND	text LIKE '%employee%'
    Yay!
    George
    Home | Blog

  7. #7
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    nevermind... looks like george answered my question
    Inspiration Through Fermentation

  8. #8
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Try this
    Code:
    SELECT	o.name, c.*
    FROM	syscomments c
    LEFT JOIN sysobjects o
    	ON o.id = c.id
    WHERE	c.id IN (SELECT id FROM sysobjects WHERE type = 'p')
    AND	c.text LIKE '%employee%'
    EDIT: yay!
    George
    Home | Blog

  9. #9
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    That seems to do it. Thanks!
    Inspiration Through Fermentation

  10. #10
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    note that the syscomments thing is not bulletproof because if the proc is greater than 4000 chars, it will wrap to a new row. this means that the string you are searching for could be cut between two rows, so this method would not work.

    bulletproof is to script out all procs and then search the files.

  11. #11
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Agree with Jez, for the reasons mentioned. Actually querying syscomments is deceptively tricky.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  12. #12
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by blindman
    Agree with Jez, for the reasons mentioned. Actually querying syscomments is deceptively tricky.
    For many reasons!

    Querying syscomments is a good "litmus test" for some things, but I don't recommend it unless you understand exactly what you are looking for, and all of the ways that the little beggar in question can hide from you, and how many ways you can hit false positives.

    I would strongly suggest scripting the procedures (possibly using DMO), then editing them (possibly using script) instead of trudging through the system tables.

    -PatP

Posting Permissions

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