How to Recompile All Database Objects

This is an example of using a cursor to recompile all stored procedures in the current database.

-- Declare a cursor to loop through all stored procedures
DECLARE sps CURSOR FOR
    SELECT ROUTINE_NAME
    FROM INFORMATION_SCHEMA.ROUTINES
    WHERE ROUTINE_TYPE = 'PROCEDURE';

OPEN sps;

DECLARE @RoutineName VARCHAR(128);
DECLARE @SQLString NVARCHAR(2048);

FETCH NEXT FROM sps INTO @RoutineName;

WHILE @@FETCH_STATUS = 0
BEGIN
    -- Prepare the RECOMPILE statement
    SET @SQLString = 'EXEC sp_recompile ''' + @RoutineName + '''';
    
    -- Optional: Print the statement being executed
    PRINT @SQLString;

    -- Execute the dynamic SQL
    EXEC sp_executesql @SQLString;

    FETCH NEXT FROM sps INTO @RoutineName;
END;

-- Clean up
CLOSE sps;
DEALLOCATE sps;