I use the term 'workhorse' to describe a theorem which is technically challenging to prove, perhaps very deep, but the statement is either uninteresting at first glance or too imposing to be understood by non-experts. I will give some examples of what I feel are 'workhorse' theorems:
1) Bombieri-Vinogradov theorem. This theorem, which is believed (according to Jean-marie De Koninck and Florian Luca's book) to be the reason for Bombieri's Fields Medal in 1974, asserts basically that the Generalized Riemann Hypothesis is true 'on average' over an impressive range of primes. The exact statement, however, is likely quite obtuse to non-number theorists. That said, Bombieri-Vinogradov has an impressive list of consequences, including the most recent results on bounded gaps between primes (due to Maynard).
2) Heath-Brown's 'Theorem 14'. Proved by Heath-Brown in his 2002 paper "The density of rational points on curves and surfaces", this theorem generalized the Bombieri-Pila determinant method to the $p$-adic setting. Its statement is long and difficult to understand at first glance, but it has enormous consequences including (ultimately shown by Salberger) the so-called dimension growth conjecture. It also provided uniform estimates for curves (the best result on this is a preprint due to Miguel Walsh Edit: I just found out that Walsh's paper has now appeared in print and can be found here: http://imrn.oxfordjournals.org/content/early/2014/06/29/imrn.rnu103.refs) and surfaces. It has consequences for concrete diophantine problems, including power-free values of polynomials and power-free values of $f(p)$ where $p$ ranges over the primes only (previous error bounds only provided $\log$ power savings, which are insufficient for this case).
So roughly speaking a workhorse theorem is one where the statement of the theorem is not intuitive nor easy to understand, its proof is difficult and perhaps not very enlightening, but nonetheless it has extraordinary consequences and can be used to prove results which are much easier to understand or seemingly unrelated.
Best Answer
As others have noted, this sort of thing is commonplace in analysis. The best results often flow directly from the strongest available estimates, and the strongest estimates are often complicated and inaccessible to the non-expert.
In more algebraic areas, you may want to look for famous results that people call "lemmas" rather than "theorems." People tend to call a result a "lemma" if it doesn't look like something you'd be interested in for its own sake, but is nevertheless useful for proving other things of interest. Now, if you are only interested in results that are deep or difficult, then not all lemmas will qualify, since some are very simple (Schur's lemma, Zorn's lemma, Yoneda's lemma) and others are non-trivial but not too difficult (Nakayama's lemma, Hensel's lemma, Sperner's lemma). However, there do exist "high-powered" examples such as the fundamental lemma or the Szemerédi regularity lemma.