Using Tasks, Generate Scripts, SQL Server has a nice wizard that allows scripting plenty of objects at once, giving the choice of 1 single file or 1 file per object, including the dependencies or not, scripting either as CREATE or DROP AND CREATE, optionally include dependent objects, triggers, indexes, etc...
The thing is, there are many options I tweak, and I would like to be able to "save" my scripting choices so I can easily repeat that scripting using the same preferences.
Is there a (simple) way to do this ? ( I am currently using 2012/Denali)
Add a comment
|
2 Answers
The SSMS way of scripting is nothing but a shell around the Scripter object. The options you set are mostly just UX for the ScriptingOptions. You can write a wrapper application that does what you want, or you can use directly PowerShell to manipulate SMO and have a 'script' that scripts your objects, according to your preferences.
That being said you should never find yourself scripting database objects. You should always do the opposite, execute source code to update objects (ie. never ever manipulate them via SSMS). See Version Control and your Database.
1 Comment
Patrick Honorez
Just looking for a way to export for deployment, in a client's place where TFS tools & usage are discouragingly counterproductive :-/
If you're deploying to an existing database (ie, upgrading), you can't just run drop/create scripts on the target, as you'd lose data. You have to generate the difference SQL script and run that instead. There are many comparison and synchronization tools to create these scripts.
4 Comments
Patrick Honorez
Database is not just tables. Over 80% of the development effort is on views, stored proc, triggers... And that has to be scripted, and it's too long to do it object by object. The wizard does that very well, but there are about 10 params I change every time to get what I want.
David Atkinson
Have you tried tools like SQL Compare before? We developed the tool specifically to satisfy this use case.
Patrick Honorez
aha, disclosing yourself ;-) I am sure you're right, but when working on one project for a client, a consultant can't always impact the way the client is organised. I am am already happy in my current contract, that I could have SQL Search installed on my own machine.
David Atkinson
:) At the very least can you ask your client to try the 14-day fully functional trial? #nothingtolose