Collapse the table of content
Expand the table of content


SQL Server 2000

Causes stored procedures and triggers to be recompiled the next time they are run.


sp_recompile [ @objname = ] 'object'


[@objname =] 'object'

Is the qualified or unqualified name of a stored procedure, trigger, table, or view in the current database. object is nvarchar(776), with no default. If object is the name of a stored procedure or trigger, the stored procedure or trigger will be recompiled the next time it is run. If object is the name of a table or view, all the stored procedures that reference the table or view will be recompiled the next time they are run.

Return Code Values

0 (success) or a nonzero number (failure)


sp_recompile looks for an object in the current database only.

The queries used by stored procedures and triggers are optimized only when they are compiled. As indexes or other changes that affect statistics are made to the database, compiled stored procedures and triggers may lose efficiency. By recompiling stored procedures and triggers that act on a table, you can reoptimize the queries.

Note  Microsoft® SQL Server™ automatically recompiles stored procedures and triggers when it is advantageous to do so.


Execute permissions default to the public role. Users that are not members of the sysadmin fixed server role or the db_owner fixed database role can affect only their own tables.


This example causes the triggers and stored procedures that uses the titles table to be recompiled the next time they are run.

EXEC sp_recompile titles

See Also

System Stored Procedures

© 2016 Microsoft