You can recompile all stored procedures in a SQL Server database with a single script using sp_MSforeachtable combined with sp_recompile. This marks every stored procedure and trigger associated with each table as requiring recompilation on next execution, without dropping or recreating any objects. It takes seconds to run and is one of the most practical maintenance tasks you can perform after significant schema or statistics changes.

Why Stored Procedures Need Recompilation

SQL Server caches execution plans for stored procedures in the plan cache. When you first execute a stored procedure, the query optimiser generates an execution plan based on the current state of your indexes, statistics, and table structures. That plan gets stored and reused for subsequent executions.

The problem is that the cached plan doesn't automatically update when your environment changes. Add a new index, rebuild an existing one, update statistics, or drop an index that the plan was relying on, and SQL Server may keep using the old cached plan. In some cases that means missed index usage. In others, it means genuinely poor query performance because the plan no longer reflects reality.

This is not a theoretical concern. In production environments, we regularly see situations where a new index has been created but queries aren't using it because the cached plan predates the index. The fix is straightforward: invalidate the cached plans and let SQL Server generate fresh ones on next execution.

What Does sp_recompile Actually Do?

sp_recompile is a system stored procedure that marks an object for recompilation. When called against a table name, it marks every stored procedure, trigger, and view that references that table as requiring recompilation. Critically, it does not immediately recompile anything. It simply sets a flag that tells SQL Server to generate a new execution plan the next time the object is executed.

This is the safe and correct approach. You're not forcing an immediate CPU spike by recompiling everything at once. The recompilations happen incrementally as each procedure is called, spreading the load across normal usage patterns.

According to Microsoft documentation, sp_recompile can accept a table name, view name, or stored procedure name as its parameter. When you pass a table name, every dependent object gets flagged. That's the behaviour we're exploiting in the script below.

When Should You Recompile All Stored Procedures?

There are specific situations where running a full recompile script across your database is the right call:

  • After updating statistics - whether via UPDATE STATISTICS, sp_updatestats, or a maintenance job
  • After adding new indexes - so the optimiser can consider them in fresh execution plans
  • After dropping or disabling indexes - plans referencing removed indexes need to be invalidated
  • After significant data volume changes - when row counts have shifted dramatically, old cardinality estimates become unreliable
  • After a SQL Server version upgrade or cumulative update - the optimiser itself may have changed, and stale plans from the old version should be replaced
  • After restoring a database to a different server - statistics and index structures may differ from the source environment

You don't need to run this after every minor change. But following any significant maintenance window involving indexes or statistics, it's a sensible step to add to your post-maintenance checklist.

The Script to Recompile All Stored Procedures

Here's the script. It's short, but understanding what it does is important before you run it in production.

-- Recompile all Stored Procedures and Triggers on a Database
USE AdventureWorks;
GO
EXEC sp_MSforeachtable @command1="EXEC sp_recompile '?'";
GO

Replace AdventureWorks with the name of your target database. The USE statement ensures you're operating in the correct database context before executing.

sp_MSforeachtable is an undocumented system stored procedure that iterates over every user table in the current database and executes the command you provide, substituting ? with the fully qualified table name. So what this script does is call sp_recompile once for every table in your database, which in turn flags all dependent stored procedures and triggers for recompilation.

On a database with hundreds of tables, this still runs in well under a minute in most cases. The actual recompilation work happens later, spread across normal query execution.

What to Watch Out For

A few things worth knowing before you run this in your environment.

sp_MSforeachtable is undocumented. Microsoft hasn't formally documented this procedure, which means there's no guarantee it will exist in future SQL Server versions. It's been present since SQL Server 2000 and is still in SQL Server 2022, but it's worth being aware of this. For environments where you need a fully supported approach, you can achieve the same result with a cursor or a WHILE loop iterating over sys.tables.

It only flags objects, it doesn't execute them. The recompilation happens on next execution. If you need to verify that fresh plans are being generated, check sys.dm_exec_cached_plans after running the script and after your first round of post-maintenance query execution.

This affects the entire database. If you only changed indexes on two or three tables, you can call sp_recompile directly on those specific tables rather than running the full script. That's a more targeted approach when the scope of your changes is narrow.

Plan cache pressure. In very large environments with hundreds of databases or thousands of stored procedures, mass recompilation can create temporary pressure on the plan cache and CPU as plans are regenerated. Schedule this during low-traffic periods or your maintenance window.

A More Targeted Alternative

If you want to recompile procedures related to a specific table rather than the entire database, call sp_recompile directly:

USE YourDatabase;
GO
EXEC sp_recompile 'dbo.YourTableName';
GO

Or if you want to recompile a specific stored procedure directly:

USE YourDatabase;
GO
EXEC sp_recompile 'dbo.YourStoredProcedureName';
GO

Both approaches are valid depending on the scope of your maintenance activity.

Key Takeaways

  • Running EXEC sp_MSforeachtable @command1="EXEC sp_recompile '?'" flags every stored procedure and trigger in a database for recompilation on next execution, without immediately forcing a CPU spike.
  • Stale execution plans are a real and common cause of performance degradation after index or statistics changes. Invalidating cached plans is a necessary post-maintenance step.
  • sp_recompile marks objects for recompilation but doesn't execute them immediately. The overhead is spread across normal usage patterns.
  • sp_MSforeachtable is undocumented. It works reliably across current SQL Server versions, but consider a cursor-based alternative if you need a fully supported approach.
  • For targeted changes, call sp_recompile directly against the specific table or procedure rather than running the full database-wide script.

If you're not sure whether stale execution plans are affecting performance in your SQL Server environment, a DBA Services health check can identify plan cache issues, missing index opportunities, and statistics that haven't been updated in too long. Our team works with SQL Server environments across Australia and can help you build a maintenance routine that keeps execution plans current and query performance where it should be. Get in touch with DBA Services to find out more.