I personally likes Prise tool. It gives you the amount of gain on the list of tables that you selected. In our system, some of the tables are already compressed, so first i run my own query to seggreage the NONMVC ones from MVC ones. And I simply select the NONMVC ones from Prise tool. It will analyze all the tables and gives a nice report with how much we are going to gain, and how many cpu cycles consumed by the tool for the analysis (by prise tool). Compare to the manual analysis the amount of cpu consumed by this tool is considerably low.
Atana compression tool also a better tool, but compare to Prise i do not feel Atana is the best option, becauase of its features (like, we can use this for very big tbls in tbs, automatic compressn helath check ...etc).
One last important thing is PRISE is far cheaper than ATANA with full site licencing for unlimited users.
My team has created a perl program to suggest MVC and algorithmic compression. I'm happy to email it to anyone interested. It's probably a bit primative compared to other tools, but has worked quite well for me.
Programming a comprehensive optimum calculation is a bit complex. Do not forget that you have to collect the value frequency statistics and mind special cases like varchar compression (510 byte limit), and byte alignment that means that the optimum cannot be calculated on column level, but on the whole table. After you got the values you have to asseble the DDL, whic can be rather boring manual (or PERL) job.
If you have to optimize more tables, I definitely suggest using ready-made tools like PRISE Compress Wizard (GUI based), that can:
Downloadable from here: http://www.prisetools.com/free-trial