Analyzing Stored Procedures - does a tool exist?

My boss wants me to go through the many stored procedures that are on our many SQL servers and generate a list of how often they are used, and then use that info to retire any stored procs that are not being used.
I did a search for tools and came across the SPUD tool at http://www.hybridx.com but the only problem with that tool is that i need to modify each stored procedure before it will start logging information on the stored proc.
Does anyone know of or use any tools that generate this sort of information in a non-invasive way? Any help would be greatly appreciated. Thanks.
There really isn't any tool that will do this sort of thing for you other
than running a constant profiler trace with sp_completed and see which ones
are called. Of coarse just because it isn't called for a day or so does not
mean it isn't used but it will give you a start and then you can narrow down
the list.
Andrew J. Kelly SQL MVP
"Sam" <Sam@.discussions.microsoft.com> wrote in message
news:6EBFC2B2-6FCC-4826-BCBA-9FFA351F2EF7@.microsoft.com...
> My boss wants me to go through the many stored procedures that are on our
many SQL servers and generate a list of how often they are used, and then
use that info to retire any stored procs that are not being used.
> I did a search for tools and came across the SPUD tool at
http://www.hybridx.com but the only problem with that tool is that i need to
modify each stored procedure before it will start logging information on the
stored proc.
> Does anyone know of or use any tools that generate this sort of
information in a non-invasive way? Any help would be greatly appreciated.
Thanks.
|||<<
> http://www.hybridx.com but the only problem with that tool is that i need
to
> modify each stored procedure before it will start logging information on
the[vbcol=seagreen]
> stored proc.
That sounds like a horrible idea to me. That creates too many opportunites
for thier code to cause problems with your code.
Andrew's suggestion of running Profiler is probably the best thing to do.
Brian
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OGTvDp$cEHA.1764@.TK2MSFTNGP10.phx.gbl...
> There really isn't any tool that will do this sort of thing for you other
> than running a constant profiler trace with sp_completed and see which
ones
> are called. Of coarse just because it isn't called for a day or so does
not
> mean it isn't used but it will give you a start and then you can narrow
down[vbcol=seagreen]
> the list.
> --
> Andrew J. Kelly SQL MVP
>
> "Sam" <Sam@.discussions.microsoft.com> wrote in message
> news:6EBFC2B2-6FCC-4826-BCBA-9FFA351F2EF7@.microsoft.com...
our
> many SQL servers and generate a list of how often they are used, and then
> use that info to retire any stored procs that are not being used.
> http://www.hybridx.com but the only problem with that tool is that i need
to
> modify each stored procedure before it will start logging information on
the
> stored proc.
> information in a non-invasive way? Any help would be greatly appreciated.
> Thanks.
>