Most mods are new multiplayer maps, or Mods for Blood Gulch. Not much in the way of single player. However, I will suggest using the Halo Cache Editor to alter the game it in minor ways as you want. (eg. weapons that shoot equipment and vehicles) It also has (this is v7) three mods built in which you can turn on and off: Homing Rockets, Spiderman (lets you run along walls and roofs) and Drive By (Can shoot the warthog turret while driving)
The only problem with the HCE is it can only swap values and items that are in a level. so you couldn't set a pistol to shoot a ghost on the Pillar of Autumn because there isn't a ghost in the Pillar of Autumn.
