Going east to west:
Riften - nice climate, interesting design. Once thieve's guild quests are complete and the guild is 'reborn', it's a great place to own a house and dispose of loot because of all the merchants in the cistern area. Balimund can also train in smithing. Also: two alchemists!
Downside: no smelter.
Windhelm - so frigid.......but: decent merchant quarter. Like dealing with niranye and the alchemist. Both the waterfront/docks and the grey quarter are wasted opportunities, however. House is.....interesting, though it feels a bit haunted. The fact that the jarl assigned me a guy for housecarl is damn irritating. Plus side: a smelter! And - Niranye's enchanter table.
Whiterun - very sunny and bright. A happy place

. Has it all - alchemist, general merchant, smelter, TWO smiths (including the best in the greater province.) Downside: I can't seem to find a thieve's guild contact to unload major portions of loot. House is nice, but limited (only alchemist table, no enchanter table.) Lydia is a fiiiiiiiiiine housecarl.
Markarth - forbidding city with an air of great menace and dread, located in a haunted, chaotic province with low, grey mountains. Veerrryy sinister, easily my least favorite city. The jarl and the silverblood family don't help.
But - there's a thieves guild contact, an alchemist, general merchant, TWO forges and a smelter. House is the least attractive, but still decent. Dude for a housecarl is a major turnoff.
Solitude - very, very attractive city. Picturesque, even more than Whiterun. Underused potential at port. Good selection of merchants; the fletcher is a great bonus. No smelter, though. House is gorgeous, probably the best of them all, and Jorlis the sword maiden is slurpalicious.
Ranked in order:
1. Riften
2. Solitude
3. Windhelm
4. Whiterun
5. Markarth
I'd say the cities most ripe for DLC expansion are Markarth and Riften. Markarth has several unexplored/unexcavated Dwemer tunnels; Riften is simply a great place for skullduggery, in addition to having access to Morrowind.