sp_recompile

Description

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

Syntax

sp_recompile objname

Parameters

Examples

Example 1

Recompiles each trigger and stored procedure that uses the table titles the next time the trigger or stored procedure is run:

sp_recompile titles

Usage

• 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.

  create index and update statistics result in minor schema changes, and this automatically recompiles stored procedures and trigger. Using sp_recompile for these cases results in redundant recompilations.

• 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 Adaptive Server versions 12.5 and earlier, sp_recompile could influence adhoc queries that you execute. Adaptive 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.

Permissions

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

Auditing

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

See also