Except Splatterhouse requires the ridiculous to create it's atmosphere, and Bulletstorm does as well, although I must admit after sitting through the demo I was pretty certain I didn't want to have to listen to an entire game's worth of the raunchy comments he was spouting.
It isn't neccesarily the cursing that's the issue in all cases, as sometimes it's just those "descriptively vulgar" words that make the whole thing less than appealing.
My friend and I played through Army of Two and were more amused by the language than anything, but he was less than accepting of Kane and Lynch because it became a case of excess.
Red Dead Redemption used most of it well, but like most R* games found a few places to go over the top.
The new Mortal Kombat is way over the top, but that's what it is so you look at it as "stupidly silly" in most cases.
Brink is "clean" as far as six and language (that we know of), but it's got it's share of blood and certainly touches some very grim concepts in the way of story, even though it doesn't do it in a heavy handed way.
