PhysX is widely used for doing physics effects, however, in the large majority of cases when it is used, it's strictly a software-based solution. Software-based physX will only run on the CPU. Another popular physics engine is Havoc, which also only runs on the CPU.
There's only a few games that are coded to actually run hardware physX (meaning the game can use an nV GPU to do physX calcs instead of the CPU). The most popular of these games are: Mafia 2, Batman AA, Mirror's Edge, Metro2033, Dark Void, Darkest of Days, and Sacred 2. In other words, there's not a lot of them.
If a game doesn't support hardware physX then a dedicated physX card will do absolutely nothing.
nVidia has a list of hardware-based physX games on their website somewhere ...