A Medium creature's Small rapier is a light weapon
A typical creature can use as a light weapon any typical one-handed weapon that's designed for a creature 1 size category littler than the new wielder. Such a weapon, however, is inappropriately sized, and the creature suffers a −2 penalty on attack rolls with that weapon because the weapon's designed for a creature 1 size category littler than the wielder. This penalty on attack rolls is cumulative if a weapon is even bigger or littler, but a creature typically can't wield a weapon if the weapon at all if the weapon would be less than light or more than 2-handed.1
To summarize, these are a Medium creature's options:
Light weapons: A light weapon wielded in two hands grants no benefit to damage. Weapons littler than this normally can't be wielded at all.
- A 2-handed weapon designed for a Tiny creature but with a −4 penalty on attack rolls.
- A 1-handed weapon designed for a Small creature but with a −2 penalty on attack rolls.
- A light weapon designed for a Medium creature.
1-handed weapons: Wielding a 1-handed weapon 2-handed can grant a benefit to damage.
- A 2-handed weapon designed for a Small creature but with a −2 penalty on attack rolls.
- A 1-handed weapon designed for a Medium creature.
- A light weapon designed for a Large creature but with a −2 penalty on attack rolls.
2-handed weapons: Wielding a 2-handed weapon can grant a benefit to damage. Weapons bigger than this normally can't be wielded at all (but exceptions abound).
- A 2-handed weapon designed for a Medium creature.
- A 1-handed weapon designed for a Large creature but with a −2 penalty on attack rolls.
- A light weapon designed for a Huge creature but with a −4 penalty on attack rolls.
For example, a normal rapier designed for a Small creature is usually a light weapon for the typical Medium creature. (I hedge here alot because of the vast number of exceptions available to Pathfinder creatures!) Also, while the example here is for a Medium creature, it can be scaled up or down for bigger and littler creatures.
Consequences of using inappropriately-sized weapons
However, using an inappropriately little—or big!—weapon can have an additional impact beyond a penalty on attack rolls (and, obviously, dealing more—or less—damage). For example, the feats Slashing Grace and Weapon Finesse and the magic item effortless lace mandate that to realize their benefits with a weapon, that weapon must be designed for a wielder of the wielder's size category. For example, a Medium creature that wields a rapier designed for a Small creature just can't benefit from the feat Weapon Finesse with that inappropriately little rapier.
As a further example, a creature can benefit from the feats Improved Critical (rapier), Weapon Focus (rapier), and Weapon Specialization (rapier) either when using a rapier designed for a creature 1 size category littler than the wielder as a light weapon or when using a rapier designed for a creature 1 size category bigger than the wielder as a 2-handed weapon, but a creature can't apply the benefit of the feat Slashing Grace nor the feat Weapon Finesse to either weapon! While a rapier is still a rapier no matter the size of the creature for which the rapier's designed, an inappropriately-sized weapon is also still inappropriately-sized, no matter the actual weapon!
On the other hand, a typical dwarf barbarian can take a fallen hill giant rogue's +1 sap that's designed for a Large creature and wield that +1 sap as a 1-handed weapon or even in two hands to realize a greater benefit from the feat Power Attack, albeit, in either case, suffering with that +1 sap a −2 penalty on attack rolls. Likewise, for example, such a dwarf barbarian could even swipe the cloud giant's assassin's dagger that's designed for a Huge creature and wield that weapon as a 2-handed weapon (only!—it's dagger is that big!) and will realize a greater benefit from the feat Power Attack, although that dwarf suffers a a substantial −4 penalty on attack rolls with such a dagger even before employing the feat.
1 A weapon's actual, for-reals size—rather than the size of wielders for which it was designed—is, typically, for a light weapon 2 size categories less than the size category of the creature for which the weapon is designed, for a 1-handed weapon 1 size category, and for a 2-handed weapon the same size category. For example, a rapier designed for a Medium creature is typically a Small object, and a rapier designed for a Small creature is typically a Tiny object.