Results 1 to 9 of 9
  1. #1
    Join Date
    Nov 2003
    Posts
    54

    Unanswered: query to identify stored procedures called by other procedures

    How would I determine what stored procedures are executing another procedure? For example, if i wanted a list of all stored procs that exec sp_dostuff? Is this possible?

    Thanks!

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    sp_depends
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Script your sprocs to a file and do a text search.
    If it's not practically useful, then it's practically useless.

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

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    You could also query the sys.comments table...
    George
    Home | Blog

  5. #5
    Join Date
    Jun 2007
    Location
    Ohio, USA
    Posts
    142
    I've got a proc that takes all the procs in the current DB and pipes them to a temp table via sp_helptext, then searches that. It's too long to post here (got an error when I tried), so PM me if you want to see it. (Though given that info, you could probably come up with your own.)

    Thanks.

    -D.
    David Maxwell
    Data Integrity? Yeah, I've heard of that...

  6. #6
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    ReadySetStop - stick it in a text file and attach it to your post; I'd be intrigued to see it
    George
    Home | Blog

  7. #7
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Hmmm...
    Code:
    SELECT p.name
         , c.text
         , p2.name As [dependency]
    FROM   sys.procedures p
     INNER
      JOIN syscomments c
        ON p.object_id = c.id
     INNER
      JOIN sys.procedures p2
        ON CharIndex(p2.name, c.text) > 0
       AND p2.object_id <> p.object_id
    George
    Home | Blog

  8. #8
    Join Date
    Jun 2007
    Location
    Ohio, USA
    Posts
    142
    Here you go. Not the quickest or cleanest, but it works.
    Attached Files Attached Files
    David Maxwell
    Data Integrity? Yeah, I've heard of that...

  9. #9
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    I wrote an app that does this and makes pretty little diagrams for dependencies. Also finds cross-database and cross-server dependencies if your db has such nasty stuff.

    see link in my sig for details.

Posting Permissions

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