sp_recompile (Transact-SQL)

THIS TOPIC APPLIES TO:yesSQL Server (starting with 2008)yesAzure SQL DatabasenoAzure SQL Data Warehouse noParallel Data Warehouse

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.

||
|-|
|Applies to: SQL Server ( SQL Server 2008 through current version), Azure SQL Database.|

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, or 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 and triggers 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  

See Also

CREATE PROCEDURE (Transact-SQL)
CREATE TRIGGER (Transact-SQL)
System Stored Procedures (Transact-SQL)