I have a lot of stored procedures and functions in database, many are not used any more and I want to remove them. How can I find them reliably ?
I have seen answer that suggests looking into plan cache. That's great start but there is note that retention of these plans has many factors involved. What are those factors ? I would not like to delete procedure of function just because plan cache is missing ( for instance: does option recompile delete plan ? ).
I have also considered using profiler ( running it for 2 months does not sound like a good idea ) or perhaps even making some script to change all procedures to write when they are being used ( that would not help with functions though ).
If it is not possible to find them this way, is there another way? Do you know of any other tool, besides profiler ? ( Log analysis would be too slow as I would need to go over 2 months of data ).
I need this data to be reliable as possible.