In addition to a core 3d package like Maya or Max, you do need a texturing program.
Substance Designer and Substance painter are quickly becoming the standards for texturing. Although some people still use Photoshop, or potentially alternatives like Mari, Zbrush, or 3dCoat.
Most artists do have sculpting applications, but you don’t need it for every project. More technical artists might use Houdini for some tech art magic.