By and large, the cumulative spectrum of TNA themes are about as memorable and flattering as the average Brendan Fraser movie. They've sucked Herculean balls. They make me want to waffle myself over the head with a manhole cover.
With that said, here is a short list of themes that do not sound like passing a kidney stone:
To be honest, I always thought Roode's original theme song was far better than the one he currently uses and was more fitting of his no-nonsense personality:
http://www.youtube.com/watch?v=4LVp3V2IRJM
Any list citing Haley's Comet-esque examples of good TNA themes would have to include this. For some reason, I prefer it to his WCW one.
http://www.youtube.com/watch?v=7yu6-njAQos
http://www.youtube.com/watch?v=p2WH4gQNLb0
It's probably the definitive theme of TNA's history, and the first 20 seconds or so are pretty memorable. With that said, this does contain the lyrics, "I'm trying to do my best to pass a test and show respect to the other guy" and "It's not evil, just like being Evil Kenevil. I jump to the left and I roll to the right, I'm trying to win the belt every night." I mean, this shit is reminiscent of the Birds of War song that Mac, Dennis, and Charlie write in the episode of Sunny where they wrestle for the troops. I'm not sure if this is a good theme or the biggest piece of shit I've ever heard.
Samoa Joe's original theme (this one) was fucking awesome. Then they changed it to a song that sounded like audio foot porn. Bad.
http://www.youtube.com/watch?v=gsEadyGc3Es
http://www.youtube.com/watch?v=luEWMDcv2oc The Motor City Machine Guns originally came out to a great knock-off of this song until Bang Bang! threatened to sue TNA, or something like that. TNA then proceeded to give them music that sounded as intimidating as a Timeflies song.
http://www.youtube.com/watch?v=G3M5i4-B63Y What a spectacular waste of a great theme on a worthless talent. Scott Hall, one of the saddest stories in wrestling history.
http://www.youtube.com/watch?v=MpliAYWQUWM Basically his WCW theme, which not even TNA could mess up. Fits Steiner perfectly.
http://www.youtube.com/watch?v=PfqK_smjPOU Shelley hated this theme, but for some reason I always loved it.
http://www.youtube.com/watch?v=1AdPahNxG6Q This goes hard. Ohhh yeah.
The best theme in TNA history is hardly even in question, though. This is probably one of the greatest themes in wrestling history and in an entirely different league from any others on this list:
http://www.youtube.com/watch?v=IQPvxM4uMHQ