Back when Oblivion came out, I simply accepted the idea that the Construction Set wouldn't ever work on a console. However, since 2006 I'v had the opportunity to use the stellar map editor for Far Cry 2 on XBOX 360, as well as Forge in Halo: Reach. Plus I've spent some time with the console version of Sims 3.
Based on my experience with these games I do think that Creation Kit could be implemented into the console versions with much success! However, I admit it would need to be simplified to some degree, and it would need to support the keyboard accessory (XBOX 360 and PS3 both have them). Content could then be shared and distributed among users with a system similar to Bungie's File Share for Halo: Reach.