Export (0) Print
Expand All
Expand Minimize

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.

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

Topic link icon Transact-SQL Syntax Conventions

sp_recompile [ @objname = ] 'object'

[ @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.

0 (success) or a nonzero number (failure)

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 Note

SQL Server automatically recompiles stored procedures, triggers, and user-defined functions when it is advantageous to do this.

Requires ALTER permission on the specified object.

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

Community Additions

ADD
Show:
© 2014 Microsoft