Ok, I'm putting together a list of decent climbing cities that are core metropolitan areas (so, no Flagstaff or Chattanooga, though those places would take the gold medal no problem).

Using the criteria of three tiers of climbing: LOCAL (within or right outside city limits, less than an hour away), DAY TRIP (within 3 hours drive), and WEEKEND (within 6-8 hours). I've starred areas I think are nationally renowned destinations.

No particular order. Feel free to criticize/add to it.

LAS VEGAS, NV
Local: Red Rocks*
Day Trip: Virgin River Gorge*, Mt. Charleston
Weekend: Jtree*, Zion*

LOS ANGELES, CA (though from what I've heard about drive time, some of the local areas might not work that well)
Local: Malibu Creek, Tick Rock, Stony Point
Day Trip: Taquitz/Suicide, Jtree*,
Weekend: Yosemite*, Needles*

SEATTLE, WA
Local: -
Day Trip: Index, Leavenworth, Vantage
Weekend: Squamish*, Smith Rock*

RENO, NV
Local:-
Day Trip: Lover's Leap, Donner
Weekend: Bishop*, Yosemite*

SALT LAKE CITY:
Local: Big and Little Cottonwood Canyons
Day Trip: Maple Canyon*, Uintas, American Fork Canyon
Weekend: City of Rocks*, Tetons*, Ibex, Joe's Valley, Red Rocks*, Indian Creek*, Zion*

DENVER, CO
Local: ?
Day Trip: shit-ton of stuff, none gets a star though
Weekend: Moab/Indian Creek*, Rifle*, Vedauvoo*, Black Canyon*

ALBUQUERQUE, NM
Local: Sandias?
Day Trip: Enchanted Tower
Weekend: Hueco Tanks*, Shelf Road, Indian Creek*

PHOENIX, AZ:
Local: -
Day Trip: Queen Creek, Homestead, Cochise Stronghold, Paradise Forks, Sedona, Priest Draw
Weekend: Red Rocks*, Jtree*, Hueco*, Indian Creek*

AUSTIN, TX:
Local: Green Belt, McKinney Falls, many others
Day Trip: Reimer's Ranch, Enchanted Rock
Weekend: Potrero Chico*, Wichitas, Continental Ranch

NASHVILLE, TN:
Local: -
Day Trip: T-wall*, Obed
Weekend: RRG*, NRG*, HP40, various NC crags

CHARLESTON, WV:
Local: -
Day Trip: New River Gorge*
Weekend: RRG*, NC, Seneca, Coopers Rock, T-wall*, HP40

ATLANTA, GA:
Local: Boatrock
Day Trip: T-wall*, Rocktown
Weekend: HP40, RRG*, NRG*, NC

WASHINGTON DC:
local: -
day trip: Carderock, Old Rag
Weekend: NRG*, NC, Seneca

CINCINATTI, OH
local: -
Day Trip: RRG*
Weekend: NRG*, T-wall*, NC, Obed

NEW YORK CITY:
Local: Rat Rock!
Day Trip: Gunks*
Weekend: Seneca, Rumney, Cathedral

That's about all I can think of off the top of my head, time to get off the computer. Someone should make a spreadsheet of these.

What about Chicago, Boston, Boise, Madison, Twin Cities?