#Recompile a stored procedure code#
GO SQL Server 2000 Code to Recompile All Objects SELECT 8 - Descend through the database list 7 - Execute the final string to complete the backups SELECT = 'EXEC sp_recompile ' + '' + char(13)
SELECT = MAX(UIDTableList) FROM 4 - While condition for looping through the database recordsįROM UIDTableList = 6 - String together the final backup command 3 - Determine the highest UIDDatabaseList to loop through the records 2 - Outer loop for populating the database names 1 - Declaration statements for all variables 002 | N\A | | JKadlec | Updated code for SQL Server 2008 R2 Rev | CMR | Date Modified | Developer | Change Summary Detailed Description: Execute sp_recompile for all tables in a database
Purpose: Execute sp_recompile for all tables in a database Business Process: SQL Server Database Maintenance Project: SQL Server Database Maintenance SQL Server 2008 R2, 20 Code to Recompile All ObjectsĬREATE PROCEDURE dbo.spEXECsp_RECOMPILE AS As such reference the scripts below as a simple means to recompile all of the objects in the database. Nevertheless, in SQL Server, I am not aware of a system stored procedure that will recompile all of the objects in a similar manner as sp_updatestats. If this is the case, it is necessary to determine when the stored procedure should be recompiled or determine if the performance is bad enough to have separated stored procedures to support the two different query plans. In a different scenario, the parameter used to build the query plan may be good for some queries and bad for others. In essence the query plan changes and the SQL Server optimizer may not be getting the best query plan based on the recent changes. By recompiling stored procedures and triggers that act on a table, you can reoptimize the queries' (Source = sp_recompile). Recompiling an object is advantageous when 'indexes or other changes that affect statistics are made to the database, compiled stored procedures and triggers may lose efficiency. Sp_recompile is a system stored procedure in SQL Server 2008 R2, 2008, 20 that will recompile an object the next time it runs.
0 kommentar(er)
