Many top SLACs recruit for at least some teams; for example, in recent years Haverford, a very selective school with a cerebral image, has recruited DC-area students for baseball, tennis, cross-country and soccer, and perhaps other teams as well. These students were very well-qualified academically, as were their less athletic classmates who were accepted, in part, because of their talents in theatre, debate, robotics or any number of other activities. While Williams might be at the extreme (I know it plays up the school's athletic record with alums through the magazine and in development communications), even Williams accepts many students who will not be varsity athletes, but who bring to the mix some other perspective or strength.
OP, if your son enjoys playing a sport and being part of a team, please encourage that. It's a great way for him to be part of his school community and to do something he loves. Moreover, when it comes time for him to apply to college, an admissions staff will understand and appreciate the commitment required to play a varsity sport even if he's not a star. Of course, you'll also want to encourage him to pursue one or two other activities where he can really shine, but, as a former admissions staffer and a parent who has gone through the process 3 times with my own children, I urge you not to succumb to either the anxiety or the cynicism that can surround it. At the end of the road, you want to feel that you have helped your child develop a sense of who he is becoming as a young adult and what kind of college experience will help him move forward in that direction.