Aside from a noisy garbage disposal, a whining furnace belt, or a squeaky floor, there is nothing that will do more to drive a homeowner nuts than obnoxious plumbing pipes.
The good news is that there is something that can be done to cut down on the noise. The bad news is that anything that you might do short of remodeling might provide only modest relief.
The problem has grown worse with modern construction where plastic pipe is used for the soils system rather than the old fashioned cast iron or galvanized which was heavier and did a better job of muffling sound. Furthermore, walls are thinner and the wallboard that covers them is a fraction of the wood lath and plaster that once lined the interiors of homes.
The most cost effective way to deal with noisy plumbing pipes is to insulate them. Water pipes which are accessible should be wrapped with the appropriate size neoprene rigid insulation. Accessible soil pipes should be wrapped with a minimum of 1" thick fiberglass insulation. In either case the insulation should be secured with duct tape.
Wall cavities that contain plumbing pipes which are not accessible should be filled with blown-in insulation. These measures may improve the condition only slightly, but may be just enough to get you through the night.