Well, I researched this exact subject a few weeks ago and here is what I came up with:
http://www.nexusmods.com/skyrim/mods/19281/?: Overhauls the entire game, including perks. Makes the game MUCH harder with a focus on roleplaying. Difficulty is achieved largely by increasing the damage done by both PC and enemies, so battles can be over quick one way or the other. Alters virtually every mechanic in the game. There are a series of https://www.youtube.com/watch?v=z1AVfsqMFZg videos linked on the Requiem site that will give you a basic idea about what this mod is all about. Requiem completely delevels the world.
http://www.nexusmods.com/skyrim/mods/9286/?: Overhauls the entire game, but not as extensively as Requiem and its modular so you can pick and choose. Adds some cool new perk tres like Fingersmith and Wayfarer, but renaming and consolidating other trees. Overall it is not as comprehensive an overhaul as Requiem but it is probably more popular. It makes the game harder but not nearly as hard as Requiem, which is probably why it is more popular. Also, the world remains leveled/scaled in SkyRe. SkyRe also has some good https://www.youtube.com/watch?v=4xVhes9uVqol videos to give you an idea what it is all about.
Sperg: Mainly a perk overhaul that does not change other elements of the game.
PerMa: Done by the same person who made SkyRe. It picks up where SkyRe left off and adds extensively to the perk trees of SkyRe. I don't think it is a comprehensive overhaul like SkyRe, but honestly, I did not research PerMa that much.
I was looking for a more comprehensive overhaul, so I spent most of my time comparing SkyRe with Requiem, and in the end I went with Requiem. I liked a lot of what I learned about SkyRe but I really liked the design philosophy behind Requiem.