Recompilation automatically takes place whenever one of the following events occur:
• The procedure is loaded from disk to the procedure cache.
• You drop an index on any table referred to in the procedure.
• All copies of the execution plan in cache are currently in use and another user wants to execute the procedure.
• You execute a procedure using the with recompile option.
• A database administrator flags a table with the sp_recompile stored procedure. This
causes Adaptive Server to re-resolve and then recompile any procedures or triggers
that access that table when they are next executed.
Dropping an index or a table referenced by a query causes Adaptive Server to mark the
affected procedure as needing re-resolution and recompilation at execution time.