While looking about for references on how to make a strong, prehensile tail, I found ProjectApex's answer to that exact question. The solution is to make the tail a muscular hydrostat, a bodily structure/limb/appendage/etc. that has no skeleton at its core (unlike a conventional limb) and no fluid core (unlike a hydrostatic skeleton). As Wikipedia so succinctly put it:
A muscular hydrostat, like a hydrostatic skeleton, relies on the fact that water is effectively incompressible at physiological pressures.
...a muscular hydrostat is composed mainly of muscle tissue.
Instead of the muscles acting upon a core of some kind, such as a fluid or a skeletal bone, the muscles act upon one another. This means that, since there's nothing solid to exert pressure on, it's exceptionally hard for a hydrostat to exert sideways force - i.e. lifting something at a right angle to itself. However, they can compress, pull, and push with incredible strength relative to their weight; octopi can break a shark's spine, and an elephant can lift nearly a third of a ton with its trunk.
Additionally, hydrostats are significantly more flexible and precise than bony limbs. After all, they can bend to their heart's content - there are no bones to get in the way. Moreover, since each component of a hydrostat is muscle, regulating that muscle by making a joint in it further up the hydrostat lets elephants do things like picking up tortilla chips without damaging them.
So, halfway through this wall of text, the question: - how long and heavy can a muscular hydrostat used as a tail get, relative to the size of the organism it's attached to?
Now, one of the first problems you might run into is blood flow, since it's hard to pump enough oxygenated blood into an exceptionally long (relative to the organism it's attached to) limb or bodily structure. I've cooked up a couple of ways to alleviate this.
For starters, you can wrap the blood vessels in the hydrostat with their own, small, toroidal rings of muscle, which help the main heart pump blood, and function in a fashion similar to peristalsis, or a blood pressure cuff; they squeeze the blood vessel in waves, resulting in the blood being forced down it. Additionally, you can implant a line of ventricles - severed from their parent heart - along the length of major blood vessels in the hydrostat, to act as booster stations keeping the flow moving. Given that the smallest heart in the world is microscopic, finding ones that are the right size for this task will not be a problem.
Balance is another problem; when you strap a 20-foot-long tail onto a tiger-sized animal, or a 3-foot one onto a chameleon-sized one, you'll find that said animal may have issues remaining stable. Assume that the organism this tail is attached to simply wraps its tail around itself when it is not in use, and that this renders the question of balance irrelevant.
- that the specific problems of blood flow and balance are not relevant to this question
- that this tail is operating under Earth-standard conditions; a 78-21 nitrogen-oxygen atmosphere, 1 atmosphere of pressure, 9.8 m/s^2 gravity, etc.
- that this tail needs to operate on land.
On top of that, while it's biological (i.e. made of meat, not plastic or silicon or metal), it's also artificial - this is something that's being fabricated in a lab, which is why it has features that seem unlikely to evolve in nature - like muscles around its blood vessels and miniature hearts along its length. This means that it is not limited by evolution; it is limited by the designs of mad gods.
I'm not interested in pondering what evolutionary pressures would lead to such a thing, nor am I interested in what it might be used for. All I'm interested is how big a muscular hydrostat used as a tail can get relative to what it's attached to.
I've researched this myself, but, so far, it seems that there's no upper limit to it - the largest muscular hydrostats, a whale's tongue and an elephant's trunk, have simply evolved in response to evolutionary pressures, rather than been designed by humans, and they're pretty big.
Answers should be grounded in reality - no magic, no theoretical physics, etc. I don't need hard science for this to be answered (hence the lack of that tag), but the best answers will explain what the limit is in terms of the tail's weight relative to the animal's weight, what the limit is in terms of the tail's length relative to the animal's bodily length, and do so in a fashion that's rooted in real biology.