Probably one of the best ones is Colosseum
1.0. Although small, it is very well put together. I personally didn't like it too much, but many others seemed to love it, so you might as well check it out.
is another, I liked it. It was executed fairly well, too.
There's not many actual mods for SP, either it's just a single level, or just skin replacements...