Recompile Stored Procedures, Views and Functions

Sometimes after massive changes in MSSQL database, there is a need to recompile all stored procedures, user-defined functions and views in the database in order to MSSQL will refresh stores procedures execution plans stored in memory in order to reflect recent schema changes. Below is a small MSSQL code snipped written solely for maintenance purposes. It goes through database objects and performs recompilation using sp_recompile system stored procedure.


DECLARE proccurs CURSOR
FOR
    SELECT [name] FROM sysobjects
WHERE xtype in ('p', 'v', 'fn')

OPEN proccurs
DECLARE @pname VARCHAR(60)

FETCH NEXT FROM proccurs INTO @pname
WHILE @@fetch_status = 0
BEGIN
    EXEC sp_recompile @pname
    FETCH NEXT FROM proccurs INTO @pname
END
CLOSE proccurs
DEALLOCATE proccurs