sp_recompile

Syntax

sp_recompile objname

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
```

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.

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:

Information	Values
Event	38
Audit option	exec_procedure
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