Chapter 1: System Procedures

sp_recompile

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

sp_recompile objname

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

sp_recompile titles

The queries used by stored procedures and triggers are optimized only once, when they are compiled. As you add indexes or make other changes to your database that affect its statistics, your compiled stored procedures and triggers may lose efficiency. By recompiling the stored procedures and triggers that act on a table, you can optimize the queries for maximum efficiency.
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).

Any user can execute sp_recompile.

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

Event	Audit option	Command or access audited	Information in extrainfo
38	exec_procedure	Execution of a procedure	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