[snip]
It boils down to, Consoles could load the mods - they couldn't run them. Stat alteration mods, perhaps same-quality retextures, but very little more. The real draw of modding is freedom, and putting it onto console would restrict it to the point where it was not the same thing at all.
Consoles could run any mods that conform to the performance restrictions of the base game - and while that isn't any and all mods, it allows for a hell of a lot. You just have to think 'DLC' rather than 'Graphics overhaul'. Think 'Shivering Isles' rather than 'Quarls Texture Pack'. Think 'Ruined Tail's Tale' rather than 'Mart's Monster Mod'.
New quests, new areas, new dungeons, new NPCs, new weapons, new armour... that's an awful lot of modding possibility there.
As for how such mods could be distributed for consoles, I suspect Sony and Microsoft might be ok with it but would charge a nominal fee for people to download the mods to cover providing storage and bandwidth. However, they may require Bethesda to do some quality control on mods submitted for console (for acceptable content as well as basic reliability), and this could be the sticking point. Bethesda would have to justify the ongoing cost of a small review team by treating the console mods as advertising, with modders providing content free and Bethesda covering administration. Bethesda might see it that way, they might not.