If you had something that would pop up and tell you, "this query involves a cross-join", or "this query will involve multiple table scans of large tables", or something like that, in the way that it will tell you about "index hints force..." and so on, in the Messages tab when you execute the create/alter command for a proc, that might be helpful. You could ignore it if it's expected/desired, but it might help catch a few of the eggregious errors.

A DBA at a prior job was having a heck of a time trying to figure out why his query was returning 25,000 rows when he was expecting more like 6. Turned out he was using single-letter table aliases in the From clause, and one of his joins used the same alias on both sides, because both tables began with the same letter. The alias was correct, so it wasn't giving an error message, but the join was "a.column = a.column", which made it a cross join. Hard to spot in the middle of a complex query, so easy for the eye to slide right past it, especially when most people see what they expect instead of what's actually there. (Over 90% of people fail on simple errors when tested for proofreading jobs. Not specific knowledge of the spelling of specific words, or judgement rulings on punctuation, but really simple stuff like "the the" being read as "the". People see what they expect.)

For that kind of thing, a warning about a cross join and the line number it appeared on would have been quite useful. Would have saved about five minutes of frustrating review. For that, a code checker could be quite nice.

Maybe RedGate should start the R&D on it. If they haven't already.