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

• The queries used by stored procedures and triggers are optimized only once, when they are compiled. When systabstats statistics such as row counts or cluster ratios change significantly, your compiled stored procedures and triggers may lose efficiency, and may benefit from sp_recompile recompilation. 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 and recompiles triggers and stored procedures only in the current database. sp_recompile does not affect objects in other databases that depend on the table.

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

  sp_recompile could still influence adhoc queries that started execution before sp_recompile was run (a concurrent execution).

Permissions

Only the Database Owner or a System Administrator can use the setuser command to assume another database user’s identity to recompile objects owned by other users. All users can execute sp_recompile to recompile their own objects.

Auditing

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

See also