For the question of lead pipes, the 2014 paper Lead in ancient Rome’s city waters, by Hugo Delile et al, probably contains more than enough detail to answer the question. The authors concluded that:
Lead pollution of “tap water” in Roman times is clearly measurable, but unlikely to have been truly harmful.
But piped water was by no means the only source of lead in the ancient Roman diet.
In 1983, the Canadian geologist Dr Jerome O. Nriagu put forward a theory that dietary lead was a major contributing factor in the fall of Ancient Rome (in the book Lead and Lead Poisoning in Antiquity (Wiley, 1983), and the paper Saturnine Gout among Roman Aristocrats — Did Lead Poisoning Contribute to the Fall of the Empire? in the New England Journal of Medicine, March 1983).
To give just one example, it was common in Ancinet Rome to sweeten wine (and many other foods) with a syrup made from boiled grapes. It was believed that the grapes were boiled in vessels made from, or lined with lead.
Experimental archaeologists replicated the process and tested the resulting syrups. They found that the syrups contained between 240 and 1000 milligrams of lead per litre.
As Dr Nriagu observed,
"One teaspoon (5ml) of such syrup would have been more than enough to cause chronic lead poisoning"
However, the effects on Rome's ruling elite that Dr Nriagu hypothesised might have been caused by chronic lead poisoning (like the symptoms exhibited by Claudius, for example) may be the result of a number of different causes. As Dr Nriagu himself observed, the causes of the maladies with which Claudius was afflicted are:
"... a matter of longstanding debate."
Dr Nriagu's conclusions were challenged almost immediately, notably by John Scarborough in his 1984 review of Dr Nriagu's work titled The Myth of Lead Poisoning among the Romans: An Essay Review, published in the Journal of the History of Medicine and Allied Sciences (Vol. 39, No. 4 (October 1984), pp. 469-475).
Although Dr Nriagu still stands by his conclusions, a number of skeletal remains from the Imperial Roman period have been excavated and tested for evidence of lead poisoning since 1983.
While there are certainly some examples that exhibit evidence of lead poisoning (or even severe lead poisoning, as Kristina Killgrove observed in the article Lead Poisoning in Rome - The Skeletal Evidence), this is by no means common. This lack of evidence of widespread lead poisoning in the archaeological skeletal record seems to fatally undermine Dr Nriagu's theory.
It is very possible that lead was used rather less in Roman cooking vessels thatthan we used to believe. It is certainly true that some ancient authors, like Pliny (Natural History, XIV, 127-135) and Columella (On Agriculture, XII, 19) for example, express a preference for lead vessels, but as Scarborough argues (in the paper cited above) this 'preference' might also suggest that
Romans most often used bronze cauldrons (copper and tin in alloy), not those of lead
This interpretation would be compatible with the archaeological evidence.
Given the lack of skeletal evidence for widespread severe lead poisoning, it seems reasonably clear that lead poisoning was not the primary cause of the Fall of Rome. The present consensus of the scientific community is briefly summarised by Hugo Delile in the paper cited at the start of this answer:
"... today lead is no longer seen as the prime culprit of Rome’s demise"
