Amongst other uses, recompiling your stored procedures can be useful especially when you want to ensure that you haven't deleted/renamed any columns/tables.
The following queries shown below recompile all stored procedures the next time they are run.
Recompiling all the Stored Procedures in a Database
-- Recompile all Stored Procedures and Triggers on a Database
USE AdventureWorks;
GO
EXEC sp_MSforeachtable @command1="EXEC sp_recompile '?'";
GO
Note: See my post over here for other uses of sp_MSforeachtable 8 Common Uses of the undocumented Stored Procedure sp_MSforeachtable
Recompile all the Stored Procedures in a Table
-- Recompile all Stored Procedures that act on the Customer table
USE AdventureWorks;
GO
EXEC sp_recompile N'Sales.Customer';
GO
Recompile a specific Stored Procedure
-- Recompile a specific Stored Procedure uspGetEmployeeManagers
USE AdventureWorks;
GO
EXEC sp_recompile 'uspGetEmployeeManagers';
GO
comments
0 Responses to "Recompiling Stored Procedures in SQL Server – All, Few or One at a time"Post a Comment