I can recommend this document

http://msdn.microsoft.com/en-us/library/dd894051(v=sql.100).aspx

because there are a few good hints about when to use which compressiontype. I am sure you can write a script to do it automatically but if you don't have a few hundred tables up and running 24-7 you should do them manually. You won't do it that often to change the compression and you can keep an eye on the growth of the logfile, especially if you have mirroring and replication in place.

I haven't used many compressed tables in a live-system at the moment but my researches are that far that page-compression is pretty nice but much more expensive so I would only use it on archive-tables or tables where there is a big difference between page-compression and row-compression, as well as fact-tables in a BI-environment.