To me, a true and classic military operation code name has very little or nothing to do with the operation itself. It does not really describe the operation, the title is seemingly random though the component words may themselves stand for something (eg. Market Garden - "market = air component, “garden” = ground component) or have related meanings, though in an obscure way. I think of it the same way with hollywood movie working titles (“Blue Harvest” = Return of the Jedi) or top secret weapons programs (“Have Blue” = Lockheed F-117 program demonstrator and “Aurora” = Northrop B-2 program).
Recent entire war-scale operation names are less interesting and more expository on the subject matter: “Operation Enduring Freedom”, “Operation Iraqi Freedom”, the proposed “Operation Inherent Resolve” … they are all so self-explanatory and pretty boring. “Operation Desert Storm/Shield/Fox” are in this category but are a little better.
The above operations are all essentially proxies for the entire wars that they represent, which I don’t think fits the intent. Operation names should be for given offensives or theater operations rather than an entire war. But I suppose if Congress is not going to declare war then you have to call it an “operation”, but I digress.
“Overlord”, “Husky”, “Olympic”, etc… all fit the bill in my opinion. “Neptune Spear” is cool; it relates to the Navy Seals, but is not as obvious as saying “Operation Kill Osama”. There were a series of Afghanistan operations that just used professional sports team names, with no real thought how they pertained to the operation. I like that because it is seemingly random (Ex. “Operation Red Wings” Lone Survivor movie Â - Detroit Red Wings).
Yes, from a security point of view a good code name for an operation should reveal nothing about the nature of the operation (unless perhaps it’s actually intended to be a deception), in the same way that a sign / countersign recognition code should never make it possible to guess the countersign from the sign (a mistake frequently made in the TV spy comedy series Get Smart). The overall D-Day code name Overlord, as you point out, fits this requirement nicely; the code name for the overall deception operation, Bodyguard, wasn’t as good, and the name for the naval component of Overlord, Neptune, was rather ill-advised. The problem I see with names like “Enduring Freedom” is that, while they sound nicely inspirational, they also sound like marketing slogans intended to “sell” the operation to a possibly sceptical public (which, admittedly, may be a useful thing to do).
By the way, I think the land operation that followed Desert Shield and Desert Storm was called Desert Saber, not Desert Fox – though I’m sure Rommel wouldn’t have minded the homage, especially given that the US troops were by that point in their history using the German-style “Fritz” helmet.