Hi guys,

I've just finished dev on a system and have about 140 stored procs. I
know some of them aren't used and want to prune them down. Is there
anyway I can do this? Maybe find the time a stored proc was last
executed or something like that?

Cheers, Pete (aka lad4bear)

No, the system does not inherently keep track of object usage.  You can log
this yourself, of course...





<lad4b***@hotmail.com> wrote in message
Show quoteHide quote

if the procs are only called from an application, you could run a profiler
trace on RPC:Starting and log it to a table.  After a sufficient log period
has passed which would reflect total usage, you could dynamically build the
unique list of used procedures and find the records in sysobjects where type
= 'P' and (status & 64) = 0 that were not referenced.

If going this route - and the application was mission critical - I would
then review that list of candidates and scan through the source code files of
the calling application(s) to ensure each one is not called.  The beauty is
that Micrsoft's desktop search engine can very quickly scan through your
source code (much faster than loading the development IDE and searching for
each one).

After the list was finalized, I would then rename them "<orignal_name>_BAK"
and give it a month to ensure none were needed.

Of course, 140 procedures is a very minor amount so you may just want to
live with a couple extra :)

Show quoteHide quote

Why is Count(*) slow?
Bad datetime values stored by SQL Server 2000
Bitwise operations on BigInt
Upsized SQLServer app 100x slower than MsAccess
Where clause in variable?