Database Publishing Wizard

So I want to formalize the creation of the databases, in entirety.  I've got a million scripts per database -- the db itself, tables, procs, views, users, perms, etc -- but, i want one script per database, inclusive of everything, in entirety. And - I want it done in my order.  :-)

You go into SSMS, choose database\Tasks\Generate Scripts -- there's no real order to how things are scripted.  Or maybe I'm doing it wrong -- but, I always have to move stuff around to get it into my order -  somewhat like this:

  tables
      indices
      constraints
      triggers
  views
  udf's
  synonyms
  procedures
  logins
     users
     roles
  privilege grants

Alphabetically within that would be fabulous, but.....  not necessarily required.  :-)
(I know... that sounds pretty anal.)

Anyway, I want one script within which the entire database is scripted, from the ground up.  The database, all of the objects, the logins/users and associated privilege grants. Data isn't necessary.


I've never used the Database Publishig Wizard, so I'm doing a little reading, and I see this ability to script passwords along with the .sql, and I figured I'd check that out. I downloaded what i thought was the latest and greatest:
http://www.microsoft.com/downloads/details.aspx?FamilyId=56E5B1C5-BF17-42E0-A410-371A838E570A&displaylang=en

but the darned thing won't touch my v2008 instances.  
   'This SQL Server version (10.0) is not supported. (Microsoft.SqlServer.ConnectionInfo)

Maybe that's not the tool to use.  Maybe there's a more clever way to do the scripting within SSMS, or even Visual Studio.  

Given the above requirements, does anybody know of a good tool out there?  Or, need I just do what I've been doing via SSMS, and reorder all my objects in the final script, as needed?

Tips, thoughts, suggestions, magic sql --- all seriously welcome.  
LVL 18
dbaSQLAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

BodestoneCommented:
Well....

In SSMS if you right click on a database, go to Tasks\Generate Scripts you can script almost any aspect into one file. Not sure if it does passwords though I've only used it for DDL and not security objects.
0
dbaSQLAuthor Commented:
Yes, as I said, I've gone thru the SSMS 'Generate Scripts' many times.  I'm just looking for either a better tool, or another means of using this one, that will give me the output, as desired.
0
BodestoneCommented:
Sorry, I missed the very top bit.

I know it looks very un-ordered but it is basically in order of possible dependence on the object level so all the tables are added before any constraints, FKs and so on. within each group of objects i am not sure what determines the order.

I have seen 3rd party ones in the past but the ordering was roughly the same. ApexSQL Script is one I haven't had a chance to play with yet but if it's as good as their editor and Doc tool then it's at least worth having a go at the free trial.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
dbaSQLAuthor Commented:
i guess i was hoping for a little trick or two, that i was unaware of.
no biggie... i'll just dig back into SSMS.
thanks for looking
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.