A bit more help from the original poster would have helped some. What I mean is, is he looking for a fleshed out world, with lots of detail, where he only need concentrate on the adventure itself? Or is he looking for modularity (not modules), etc, and so on.
I'm going to assume that the poster is a time short, or lazy DM, needing a fleshed out world to adventure in.
Which brings to mind 3 campaign settings I have played AND ran in. I can only talk fully about a CS that I have experience with. Others may look nice, but, without delving into those worlds, one can never be too sure...
Remember, I'm a person who only see's the good sides of printed material. What I CAN use. All else is tossed aside.
Then, I have a word or two about a CS in general...
Forgotten Realms. I ran this for 6 straight years, and on and off again for many more, during the end of 1E, and all through the 2E years. I'll say now, I dislike what has been done to the FRCS for 3E. My campaign was based in Cormyr. I'll not have King Azoun IV dead in my game, but I digress for now ...
FRCS: ALOT of source material. Novels for additional flavor.
IMO, a good detailed CS is invaluable. IF you want good geography, a well defined pantheon, a good overall development of the various interpersonal relationships between the various nations, a long history for reference, well done maps (one of MY main issues), specialized sourcebooks to add additional flavor/information, then FRCS is a very viable candidate. Good overall support, and probably the most popular setting ever, GLOBALLY speaking.
Best city book EVER done in print: Waterdeep and the North: $9.95 MSRP
Cons: ALOT of good material is OOP, otherwise, none. See below.
Scarred Lands: Again, a well fleshed out setting, albeit alot newer than the FRCS.
You get a good amount of additional sourcebooks, and city books, with more to come. Players Guides, which define the various class roles in more minute detail. Maps are fair and useable. The setting has the underlying theme of a world reborn from devastation, where things are still 'settling in' so to speak. You get a good mix of Hero's, and Tyrants. A player can feel that he can actually make a difference, flesh out his own destiny, and possibly, even his own kingdom.
It has a dark and gritty feel, yet, one can see light at the end of the tunnel. The fact that the tunnel occasionally elongates, well, that adds to the flavor, IMO.
Good geography, and pantheon, and GREAT backdrop/timeline.
Cons: None. See below.
PlaneScape: Still, one of my favorite CS's. But, the caveat here, is one must like the Planar feel to the game. This CS uses the Planes as it's core. True, one can make an entire campaign on ONE plane, thus, not using the planeS themselves. But, the flavor is just that: the planes.
ALOT of material. One knows exactly whats going on in the planar setting when one reads this material.
Cons: Out of Print, otherwise, None. See below.
#####
Ok, heres the 'See Below' part.
The overall discussion of Campaign Settings themselves.
I personally see merit in EVERY CS there is. Why...???
Because if YOU like a CS, well, thats all that matters, isn't it. You like the feel, the sources, the core books, the maps, the NPC's or whatever.
Where I'm going, is simply, WHY, oh WHY, do people say such derogatory things about any CS, when a CS is the same as any other CS in one regard: Use what you will, toss the rest aside.
I've yet to read ANY CS book that told me 'This MUST be run like thus'. Never once.
I've seen too many times, where people knock say, the FRCS.
Metagaming
MetaPlot
Meta NPC's
Elminster, Drizzt
The Chosen
Too much material.
MetaPlot novels
And alot more..
I say BAH!
As a long time DM in the FRCS, it never seems to amuse me when people make the claims, and others, as I listed above, as reasons to NOT like the Realms.
The CS has nice maps, geography, pantheon, and timeline. For a DM who wants this core list pre-made for them, the FRCS is a great choice.
*** ALL the rest is up to the DM***
The DM gets to choose if Elminster is even going to be in the game. Or the Chosen of Mystra.
The DM chooses to follow the novels, or, to make up his own timeline of events as his group matures in the CS.
If you feel that you HAVE to run Elminster just because he exists in the books, then you are missing out on one of the core themes of D&D: rule 0.
I guess what irks me, are the reasons people will build a case against this or that CS. It's not that all CS's have to appeal to all people. This is not what I'm trying to say.
But when you cry foul on a CS because it has a stereotypical uber-powerful NPC listed in the 'who's who' section of a sourcebook, it in NO WAY means you have to introduce him.
I mentioned above, that I didn't care for the 3E changes made to the FRCS. That is so. I like Cormyr the way it was in the 2E version of the CS.
IF and when I run another FRCS game, I'll use what I want from the 3E FRCS, but, I can guarantee you that MY Cormyr will still be run by Azoun IV, and Tilverton will still be there as it was when it was annexed by Cormyr. No Moonstars, and Kelben will still be a Harper.
Whether or not Kelben is to be of ANY influence to my game, in my mind, Kelben will be as he was in the 2E rendition.
The Chosen of Mystra will have no more bearing in my game as it did when I ran FRCS: which was NONE. My players once asked when the next time Mystra will 'choose'. I got a good laugh, and we moved on. My players only read about the Chosen. They might have known about Elminster, Alustriel, and the sisters, and what not, but, what they read in the safety of their homes is NOT necessarily what will crop up in my game.
Taking the novels for the FRCS. MY group found the Sword of Cymrich Hugh, from the Moonshae's. Yes, they DID give it back to Tristan, but not before slapping down some Veerbeeg's with it. They met Tristan, briefly, and never saw him again.
Another example. Look at Tessaril Winter. The head chick in the module/sourcebook, Haunted Halls of Eveningstar. She is NOT a powerful mage. She can hold her own, but, if you use this source as a base of operations, your party will eventually pass up Tess in power. Do you not ramp up the NPC to hang with the party? Or will you have a lord of a town become fodder for your potentially unruly PC's?
If you ramp her up, you just 'broke canon'. Oh oh.
But if you did that, then WHY can't you just ignore Elminster? Or ignore the novel story lines if you don't like them?
Or NOT buy a particular source if you feel it's not for you?
Same for any setting. YOU do what you want with it. I just see the same old reasons why this or that setting gets the thumbs down. And the biggest two reasons are NPC's, and novels, followed by a few other mainstays.
If novels and NPC's irk you, don't use them. If you can get past your exteme HATE of Elminster, and look at the setting itself, I'm sure ANY DM will see more merit than they previously had.
Oh, this is NOT a defense of the FRCS per se. I hope you caught that, though it DOES seem like a defense of that setting. I'd say the same for any setting, though.
If, and when I go negative on any setting, it would never be due to a few NPC's, or a few novels, and related issues.
What is a setting?
A world. A world has geography. A climate. Physical things.
We have earth. IT has geography, and a climate. Just like a CS, Earth has pantheons. Some subscribe to THIS pantheon, some to THAT pantheon. It's still a world.
Wipe out the asian continent, and it's still earth. The geography has changed, but the world is still a world.
I guess it just irks me when the same old reasons for downing a CS crop up. And to me, they're weak reasons, when referring to NPC's, novels, excessive sourcebooks, etc.
Find REAL reasons to dislike a CS. Things you CANNOT take out. But there again, it depends on the amount of detail you want from a CS in the first place.
If you want geography, maps, and thats it: any CS should do you good. Find a map where the shape of a continent strikes you, and go.
IF you want flavor, well, that just became something that cannot be taken out, if you want to run that CS because of IT's flavor. If you expect a flavor in every campaign, obviously you're not going to like all flavors. NOW, we have a common thread to not like a CS.
If you want DEEP flavor, high detail, good support, a fair or better allotment of sourcebooks, then you're going to subscribe to that CS. You like what you see, what you read, and what you feel.
You'll like that CS, and dislike others, because they do not deliver what you need.
But all this time, you should NOT make your decisions based on what a novel says, or what a certain NPC has going on. Those can easily be erased from ANY and all CS's without breaking a thing.
Use Oathbound as an example. You like the Planescape'esque planar feel to the CS. You like the Feathered Fowl, and the rest of the CS dogma. It's now your CS.
OR ....
You read the Oathbound setting. You dont like the Feathered Fowl. So, you discount the entire setting.
What could be a most excellent setting, where the Feathered Fowl can be taken out, and the setting run using the rest of the source material, with you the DM modifying the CS to handle the missing Feathered Fowl, was cast aside because of 7 uber-powerful NPC's. Granted, it would NOT be the Oathbound the author planned, but it would be YOUR Oathbound.
Maybe I'm just a person who see's merit, where others see a void. I look at ALL the FRCS material available through the years. To me, thats just more stuff for me to reference. I look at what I CAN use, or MAY use, and toss the rest aside.
But, it's important to note, also, that I'm in NO WAY a homebrewer. I commend those who are, having the time to flesh out all those details.
I work full time, and have other interests besides D&D. I NEED a world where things are largely done for me, the essentials at least.
I need a map, geography, a pantheon, a few NPC's, a few POI's, and a few plot hooks, and I'm off. All the rest is bonus material, for me to expand my world, and make it larger to my group.
This allows me to put a good adventure together. I can't do both world building, and adventure building, with my allotted time.
If I had all the time in the world, however, to make my own world, I'd STILL use the FRCS, or Scarred Lands, as my core CS. The GOOD is well done enough where in my eyes, it's excellent. The bad simply gets ignored. Like the overhaul of Cormyr.
Lastly, novels. To me, they're support product, at best. They're also an additional product that the publisher is banking on you, a fan, will purchase.
Some are well written, some are hapharzardly assembled.
But to me, all support novels have their place. And not necessarily as a source of Canon.
Using the FRCS again, as the core for my example. There are the Knights of Myth Drannor. A pro-good group, linked to several uber-NPC's, yet, are also a stand alone adventuring group.
Perchance, you want to have an encounter with them in your game.
By my reading of Spellfire, I got a good look at the personalities of the NPC's in the KoMD. How they constantly have fun amongst eachother, even while staring down a Dracolich. The comraderie they share. The tossing of insults at eachother in a good fun way.
This gave me alot of good insight as to roleplaying them, without me having to dedicate time to making up their personnae myself. I got enough of that when my group wants to teleport to Waterdeep from Cormyr for a day's shopping, having to pull 20+ shopkeepers personalities/mannerism's out of my butt on the fly.
If one can imagine a city the size and scope of Waterdeep, whoa. How does a city THAT LARGE feel to a PC?
Well, read up some of the Avatar Trilogy, or the tales of Danillo Than and Aurilyn Moonblade, and one can begin to see what it's like to be in that city. Back that up with the Waterdeep and the North sourcebook, and you can run such a kick-butt city, that your players will talk about it for years to come.
I run the SLCS these days, and my group still to this day occasionally asks if they can go to Waterdeep, the TRUE City of Spendors. They ask such mainly tongue in cheek, but, it still feels good.
The SLCS has novels too. Some contend they're not that well written. To this, I agree, they're not R.A. Salvatore comparable. But, they ARE good enough, overall, for setting flavor and insight.
To those who know the SLCS, without reading the trilogy, do you know how Kadum responds/acts/bahaves? True, you can make him up. But, the trilogy gives you insight to Kadum. No other spoilers ...
Same for Balsameth. What a buddinsky. She meddles in everything not tied down, and seeks to tie everything down. Again, the trilogy gives you good insight to playing this deity, if you are to have her with a place in your game.
Do you know the despair the Forsaken have been feeling since Chern crapped all over them? The trilogy gives you Vladawen (sp), a forsaken who laid the final blow on a titan, and who gives you great insight to the despair he and his people feel from the curse upon their race. The CS sourcebooks explain this despair, but you start to FEEL it when you read the books.
I dont know. Maybe it's my wanting to provoke emotions in my players. I like my games to have depth, and great feel. In times past, I have made various players have nightmares when I previously ran a serious demonic encounter. I've had players cry over roleplaying an encounter. I look at the emotional level of the game. I use emotion to make things seem more realistic. And I get help from anywhere, and everywhere I can.
Yet, if I didn't like Vladawen the Forsaken, I can: change his personality, or totally ignore him.
But NOT trash the entire setting because he exists. In a novel.
But that's cool. 
