I'm bad at wording things, heh. When I say "Mostly dead" I mean lots have been disregarded. Also by mostly I meant some still have merit, and quite a few have been 'developed' and improved beyond all recognision. A lot also need to be shot for being unscientific and non-falsifiable (arguably along with 99% of all psychology).
By incomplete metaphor I meant (if memory again serves) the Super Ego concept is regarded nowadays as a "metaphor" in that it is a useful way in certain situations of thinking about it, but not actually how it works and not complete enough to be taken as a 'golden rule'. They is no 'super ego', but it helps at times to think as if they is.
Freud is often said to have, after one of his lectures, to have responded "Sometimes a pipe is just a pipe" to a question about what does the pipe he always has in his mouth therefore represent about him. Not sure if it actually happened, hence why I bracketed that part.
You have to admit though, Freud had sex on the brain 
Not entirely sure what you meant at the end of that first paragraph, but some of Freud's ideas have been disregarded by most, I'm sure. It's just I believe he's still more relevant than you seem to be claiming. Also, I'm pretty certain that some new theories have built from Freud's theories, but Freud's theories are still intact. Whatever alterations, unless made by Freud himself, would be considered separate theories.
I don't believe what you're saying about the Super Ego is actually true. I can't be certain (I might ask my Psychology teacher next time I get the chance about Freud's modern relevance), but I'm pretty sure Freud's Psychoanalytic theory hasn't been disregarded outright. Aspects of it may have been questioned, but I think the Id, Ego and Super Ego are still considered legitimate and relevant to the studies of personality and behaviour. As you said though, it is still sometimes a useful way to look at it (which I think was pretty much always the intention), and that's why I brought it up. Animals seem to possess an Id and an Ego, but are lacking a Super Ego (which would slightly alter the purpose of the Ego, so maybe I'm not looking at it quite right).
It sounds to me though, that if he said "Sometimes a pipe is just a pipe", it could have been for two reasons (other than the hypocrite one
). He could have been saying it in a sort of tongue-in-cheek, question evasion manner, or he could have been saying that some things do indeed not have such deep, subconscious explanations as he had previously stated, maybe both. This point isn't entirely relevant though.
On your last point, I definitely agree. Freud was an incredibly creepy old man.
