Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2004
    Posts
    10

    Question Unanswered: SQL Code control

    Is anybody aware of how I can audit redundant Stored Procedures [SP] out of a large application. I only have the SQL-Server (no Sourcesafe/CVS). I need to know when an SP was last accessed. There are over seven hundered SPs.

    Any help appreciated

    Neil.
    Thanks!!

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    About the only way to do this is to place a block of code at the start of each sproc to log it's execution in an event table. You can also plumb the system tables to find which sprocs are reference by other sprocs, but this is not 100% accurate as it may miss sprocs referenced in dynamic sql.
    If it's not practically useful, then it's practically useless.

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

  3. #3
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by Belmann
    Is anybody aware of how I can audit redundant Stored Procedures [SP] out of a large application. I only have the SQL-Server (no Sourcesafe/CVS). I need to know when an SP was last accessed. There are over seven hundered SPs.

    Any help appreciated

    Neil.
    What are "redundant Stored Procedures"?

    Did you try SQL Profiler and run a trace?
    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.

  4. #4
    Join Date
    Dec 2002
    Posts
    1,245
    Quote Originally Posted by Belmann

    Any help appreciated

    Neil.
    You might consider browsing the syscacheobjects table where objtype = 'Proc'

    Regards,

    hmscott
    Have you hugged your backup today?

Posting Permissions

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