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.

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.

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

Reference

CREATE PROCEDURE (Transact-SQL)

CREATE TRIGGER (Transact-SQL)

System Stored Procedures (Transact-SQL)