Most of the entertainers (singers, piano players etc) will stop by 1AM. I have been in the casino until 3AM and only left because I had to be on an excursion by 8 the next morning.. I don't think they ever shut off the slots unless they are coming into port. The Disco is open late (or early I guess) as others point out.
So, there is something to do almost 24 hrs a day, but you HAVE to sleep at some point (trust me... I've tried going without and you just can't do it)
Carnival Cruise Lines:
Fantasy 90, Celebration 95, Victory 05, Conquest 05, Spirit 6/06, Freedom 6/08, Holiday 7/08, Victory 6/09, Valor 9/09, Dream 6/10, Splendor 8/10,Conquest 8/11,Magic 11/11,Triumph 4/12,Spirit 7/12,Freedom 11/12, Triumph 2/13 - The fire cruise
NCL Dream October 2006, RCCL Mariner OTS June 2007