If I've do sp_recompile on all procedures in a database, can I tell what procs are pending recompile?
I'm looking into this as a possible way to tell what procs are dead code - mark all procs for recompile, then come back in a week.
Anything that's been recompiled is live code, anything still marked for recompile can be looked at further.
I've looked at the code in master.sys.sp_recompile, and I dead-end at
"EXEC %%Object(MultiName = @objname).LockMatchID(ID = @objid, Exclusive = 1, BindInternal = 0)".
I try tracing sp_recompile, and this particular statement doesn't generate any further details to give a clue what it's touching.
I don't see any new locks created by sp_recompile, so it doesn't seem to use any visible locks as its marker.