You could say the same thing about a ton of different weaponry. For instance automatics mean that a guy can walk into a room and kill and maim dozens within seconds, a very real and terrible possibility, yet we often see settings going on about how stupidly fast their guns can shoot and how the big the holes they can punch into people are.
This kind of thing is not at all exclusive to nukes. Thanks to a combination of years of action-fuck yeah style media and the Western world not suffering a extremely grueling, long conflict (on a national level, to the extent of conscription/drafts) in that time, weaponry and violence has effectively become trivialized and we don't see how terrible their effects can be until we a) see something as gripping and horrible as the Terminator scene, or b) experience it firsthand.
That said, so long as we don't lose sight of what weapons like nukes can do, I don't think you have to depict nukes as what they are and what nuclear war entails whenever you bring them up within a work, unless the theme of realistic nuclear war is most definitely what the setting is trying to convey first and foremost (in that case, just as you said, show us it's horrors and don't hold back or trivialize. If you're gonna focus on something so heavy, make it fucking heavy).
CoD games and Mass Effect aren't solely about nuclear wars so it simply wouldn't make much sense to have big banners going on about how terrible nukes are throughout. Let's teach kids about Hiroshima, Nagasaki and the Cold War so they'll hopefully know to not repeat those events, but we don't have to go on and on about how bad nukes are in any work that features them.
I thought the nuke thing in COD4 was pretty good. It wasn't that 'greatest and most gripping deconstruction of the nature of modern warfare since Platoon' or whatever variety of untrue pretentious crap some people go on about with regards to it, but I thought it was pretty well done and gave you a good incentive to stop the nukes later in the game after seeing the effect of one firsthand. I thought that was the whole idea. I was more determined to race through the final sections of the game as quickly as possible because I didn't want to see another nuke go off, not because I just wanted to get the game over with. A story event had a fairly significant impact on how I was playing the game. There's very few games where I've felt that, at least consciously.
(also, I groaned at how bored I was with MW2's airport shooting and laughed at the gas bombing. Now those were pretty fucking poorly done. Doesn't sour my view on the nuke in COD4 though.)
I'm not sure about the 40k bit. Whereas in Terminator the threat of nuclear war is first and firstmost meant to be dramatic and fucking scary (which it is, something akin to it is an occassionally recurring nightmare of mine) in 40k its moreso just to highly the Imperium's stupidity, (nuking a minor chaos infestation because why the fuck not?) or sheer desperation (nuking a planet overrun by swarms of bad shit). You're not meant to go eyes wide processing how fucking terrible WMD's are, you meant to go 'huh, that's dumb' or 'huh, that's full-on.'
tl:dr - The full effects of a nuclear conflict need only be portrayed/conveyed if that's what the story focuses on most prominently. Nukes, and all weapons are fucking bad. I think it's ok to use nukes as devices of sorts within works so long as we don't lose sight of that fact.