Causes each stored procedure and trigger that uses the named table to be recompiled the next time it runs.


sp_recompile objname




There are additional considerations when using sp_recompile:
  • Compilation involves the optimizer creating a query plan that is stored in procedure cache from the normalized query tree stored in sysprocedures. This occurs whenever a procedure or trigger is executed and no free plan for it is found in procedure cache. As you add indexes or make other changes to your database that affect its statistics, these query plans may lose efficiency. By recompiling the stored procedures and triggers that act on a table, you can optimize the queries for maximum efficiency.

    Note: Do not run sp_recompile when executing create index or update statistics. These commands results in minor schema changes, which then automatically recompile stored procedures and triggers that reference the target table on next execution.
  • sp_recompile looks for objname only in the current database. Running it causes triggers and stored procedures that reference objname to recompile the next time they are executed.

  • You cannot use sp_recompile on system tables.

  • In SAP ASE versions 12.5 and earlier, sp_recompile could influence adhoc queries that you execute. The SAP ASE server would return a schema change error (error number 540), and abort the adhoc query. sp_recompile no longer affects such adhoc queries, and you no longer see error 540.

See also:
  • create index, update statistics in Reference Manual: Commands


Any user can execute sp_recompile. Permission checks do not differ based on the granular permissions settings.


Values in event and extrainfo columns from the sysaudits table are:



Audit option


Command or access audited

Execution of a procedure

Information in extrainfo
  • Roles – Current active roles

  • Keywords or options – NULL

  • Previous value – NULL

  • Current value – NULL

  • Other information – All input parameters

  • Proxy information – Original login name, if set proxy in effect