I would say outside of Burial Mounds, I can't outright call any of Halo 2's maps horrible. Even the weaker maps (Headlong, Waterworks) aren't even that bad and can still offer a good time given the right gametype.
They excelled at the specific gametype they were good at but stunk at anything else.
Headlong? Dreamy for 1 Flag and 1 Bomb. Crap at Slayer.
Turf? A map designed for 3 Plots and it showed. 1 Flag was corpsecap central and 1 Bomb was "you're getting spawncamped" (had a similar issue to Burial Mounds with the exposed objective point/spawn). I guess Slayer worked.
Coag was good at 1/2Flag and 1/2Bomb variants, but a total stinker for KotH, Territories, Oddball..
Relic was even more of a one-trick pony and was pretty much THE map for 1 Flag CTF. For anything else, it was a bore. Also the only BTB map I can say with a straight face that got broken by BR starts in Objective. So open and flat, not fun. You could have fun with 3 plots but it still heavily favored the base team.
Containment was pretty much only good for BTB and 2 Flag at that, with Territories coming second. Bungie never gave it a chance at 1 flag because they just deleted the gametype off of it instead of giving you more than 3 minutes to get across the map (2 and half spent getting there in the first place).
We all love Terminal but once again, it was pretty much 1 Bomb and 1 Flag only.
Colossus had a nice idea with a terrible weapon layout. It had one sniper rifle and the sniper rifle was in roughly the best spot to use the sniper rifle. Also the other team spawned against an open back wall. If they had put the sniper where OS spawned and not had OS at all, things would have been a lot better.
Boneyard and Breakpoint suffer the same way, they're stellar at Invasion and are terrible at anything else. Spire at least works for Slayer. Objectives on Breakpoint are a joke.
Some of the more recent Halo 3/Reach/4 maps are a jack of all trades and work for more gametypes, but not really a master at any of them.
Also special mention for Tombstone as it was the only Halo 2 map to ever support all 8 territories in the gametype settings, which was loads of fun to play a gametype on where it was 8 teams of 2 or 5 teams of 3 , with 8 capture points and 30 minutes of cap time to win (sounds like a lot, but when you started capturing even half the points your timer would start taking off)