sp_recompile (Transact-SQL)
Causes stored procedures, triggers, and user-defined functions to be recompiled the next time that they are run. It does this by dropping the existing plan from the procedure cache forcing a new plan to be created the next time that the procedure or trigger is run. In a SQL Server Profiler collection, the event SP:CacheInsert is logged instead of the event SP:Recompile.
.gif "Topic link icon")
Transact-SQL Syntax Conventions
Syntax
sp_recompile [ @objname = ] 'object'
Arguments
- [ @objname= ] 'object'
The qualified or unqualified name of a stored procedure, trigger, table, view, or user-defined function in the current database. object is nvarchar(776), with no default. If object is the name of a stored procedure, trigger, or user-defined function, the stored procedure, trigger, or function will be recompiled the next time that it is run. If object is the name of a table or view, all the stored procedures, triggers, or user-defined functions that reference the table or view will be recompiled the next time that they are run.
Return Code Values
0 (success) or a nonzero number (failure)
Remarks
sp_recompile looks for an object in the current database only.
The queries used by stored procedures, triggers, and user-defined functions are optimized only when they are compiled. As indexes or other changes that affect statistics are made to the database, compiled stored procedures, triggers, and user-defined functions may lose efficiency. By recompiling stored procedures, triggers, and user-defined functions that act on a table, you can reoptimize the queries.
Note
SQL Server automatically recompiles stored procedures, triggers, and user-defined functions when it is advantageous to do this.
Permissions
Requires ALTER permission on the specified object.
Examples
The following example causes stored procedures, triggers, and user-defined functions that act on the Customer table to be recompiled the next time that they are run.
USE AdventureWorks2012;
GO
EXEC sp_recompile N'Sales.Customer';
GO