So far I've been to Tuscaloosa, Athens, Lexington, Auburn, Nashville, and Columbia. Here's how I rank them:
1. Tuscaloosa - Mainly because of Bama's tradition and their rivalry with Tennessee. I've been there twice, and the fans have been pretty nice. The campus is beautiful and the drive down isn't too bad. Just a fun experience overall.
2a. Athens - By far the best in terms of pre-game fun. Downtown Athens is a pretty cool place (wish we had something similar in Knoxville), with lots of neat shops and restaurants right next to campus. Also gets points due to proximity to Atlanta, where you can stay the night before and get in some shopping/eating in the big city. For the most part, though, their fans are jackasses.
2b. Auburn - Without a doubt the best "in-game" experience. Their fans are awesome! I only wish UT fans showed as much passion. Auburn (the city) isn't much to talk about. Campus is pretty nice, but not as nice as Bama. You also get the ATL benefit (see above).
3. Columbia - Going to a S.C. game is more like going to a pro game, in my opinion. Not bad, but not quite as fun as tradition-laden college game experiences. There's really not much around the stadium (as it's in some sort of what looks like an industrial park next to campus). They do have a cool "Cock-a-boose" (or whatever they call it) that is a train their fans tailgate in. Pretty neat. On the bright side, their fans are really nice and complimentary. And they leave VERY early if the Cocks are down ... too early.
4. Nashville - Stadium sucks, but it's more like a home game ... and at least you have all that Nashville has to offer.
5. Lexington - Not really all that fun at all. We always play them at the end of the year, so it's usually cold. There's really not much to do. The fans are ok, but it's just sort of a blah trip.