The sweet spot of a lens is probably just as dependent upon the type of image capturing surface used as the lens itself. Both film and digital sensors have a limit of detail they can resolve (although large-format film has the tendency to capture FAR more detail than 35mm or digital sensors at much tighter apertures, around f/22.) Assuming you have a lens with the best resolution imaginable...it will ultimately be limited by the imaging material. This is due to the "diffraction limit" of the film or sensor.
The mechanics behind finding the "sweet spot" of a lens can be fairly complex, as it is very mathematical. To simplify this for consumers, the MTF (modulation transfer function) chart was born as a way to provide clear, mathematically derived information about the sharpness, or resolution, of a lens, film, or sensor. If you are interested in the underlying theory, this article is a good read: Understanding image sharpness.
In simpler terms, assuming you want the maximum clarity for the sensor size and density you are using, for most DSLR image sensors the "sweet spot" of most lenses of decent to high quality is between f/8 and f/11. Entry-level DSLR's, which tend to have smaller sensors with smaller photosites of greater density, are diffraction limited at around f/8 or f/9. Higher-end DSLR's, which tend to have larger sensors with larger photosites and lower density, are diffraction limited around f/11.
Outside of having a really crappy lens that does not have the greatest intrinsic resolution, most lenses can resolve a high degree of fine detail. Most lenses on the market these days have their own MTF chart that can be helpful in knowing the lenses "sweet spot" in and of itself. Most digital cameras have information about when the sensor becomes diffraction limited. Review sites such as DPReview.com, the-digital-picture.com, etc. will also state the apertures at which the sensor becomes diffraction limited for most cameras. I don't do much film myself, so I can't offer you much regarding when various types of film may become diffraction limited.
It should be noted that the diffraction limiting aperture (DLA) is only when diffraction starts affecting quality, but not when it has reached its maximum effect (which is usually several stops beyond the DLA.) Visible image softening from diffraction will usually not be apparent until a couple stops beyond the initial DLA. For sensors of a given size (i.e. APS-C), a higher-density sensor will start revealing diffraction earlier, however the lower-density sensor will be incapable of resolving detail as high as the sensor of greater density. For any given megapixel size (i.e. 18mp), a sensor with larger physical size will usually provide better results. Diffraction affects image quality due to light dispersing beyond a single photosite and affecting others. As larger sensors (i.e. Full-Frame vs. APS-C) have larger photosites, they become diffraction limited at tighter apertures than smaller sensors.
The real trick is finding the overlap between the point of peak sharpness for the lens, and the point at which an image sensor is able to resolve clear detail without visibly softening it due to diffraction. An aperture setting in the overlap area will be the true "sweet spot" of the camera and lens you are using. On the flip side, if depth of field is more important than ultimate sharpness, then a higher aperture may provide a sweet spot more appropriate to your work.