Causes each stored procedure and trigger that uses the named table to be recompiled the next time it runs.
sp_recompile objname
is the name of a table in the current database.
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.
Commands create index, update statistics
