You've got two questions in there.
First, is there any way to see the real schema? Answer: not now, but I'd question why you needed it. If you had a really good reason and you explained that to the team, they might relent. (I haven't seen a good reason yet.)
Second, how does it scale so well? Answer: first, read the HighScalability article about StackOverflow here:
That explains a lot of it. No sharding is involved - the extra database servers are just used for failover. They use one database per site for the trilogy, but I can't speak for StackExchange.
Scaling a database is pretty simple: pay really close attention to your queries, and do as little work as possible inside the database. Cache as much as you can on the app side, and don't force the database server to do things it's not designed to scale. Just because Microsoft puts a feature into SQL Server doesn't mean it's designed to scale.