Generating Script in sql2005

Previosly, in SqlServer2000, when i was putting database changes (stored procedures) to the production server, I used to generate script for all stored procedures on the source server and run it it on the production server. It had in itself all the "If Exist--drop" rows, so it was posible to copy the new ones and change the existing stored procedures .

Now I'm using SqlServer2005 Express and when i want to generate sript, that "If Exist--drop" is missing so i have trouble changing all stored procedures in few steps. My question is: How can i get the script to contain those "If Exist--drop" automaticaly?

thanks

Yes. Management Studio uses the following logic,

When you create, "Script Behavior” = “Generate Create Statement Only”

If you say “Include If NOT Exists” option as TRUE then it Generate the following template

IF NOT EXISTS (SELECT * FROM ..

BEGIN

CREATE ..

END

When you create, "Script Behavior” = “Generate Drop Statement Only”

If you say “Include If NOT Exists” option as TRUE then it Generate the following template

IF EXISTS (SELECT * FROM ..

BEGIN

DROP ..

END

Missing in Console:

When you create, "Script Behavior” = “Generate Drop Statements Followed By Create Statement”

If you say “Include If NOT Exists” option as TRUE then it Generate the following template

IF EXISTS (SELECT * FROM ..

BEGIN

DROP ..

END

CREATE ..

Since the above option is missing (may be bug), first generate the drop script & append with Create generation Script with/without If Not Exist.

|||thanks|||

(This is a related question, so I'll borrow the thread)

Is there a way to save the scripting options for the script wizard or customize the default options?

For various reasons (as this thread points out) I use script generaiton options changed from the defualts as:

Include Descriptive Headers = FALSE
Include If NOT EXISTS = True
Script Drop = True
Script USE DATABASE = FALSE
Script Indexes = True

Can I change my Script Generation Wizard defaults so I don't have to select these options each time?

|||

michaelplevy wrote:

Is there a way to save the scripting options for the script wizard or customize the default options?

With SQL Server 2005 Service Pack 2, we've introduced the Tools -> Options... -> Scripting dialog so that you can change the behavior of how Management Studio generates scripts. These settings will be the default settings for the Generate Script Wizard.

Does this help?

Paul A. Mestemaker II

Program Manager

Microsoft SQL Server

http://blogs.msdn.com/sqlrem/

|||Paul, thanks for the tip about changing the script options.|||Hello,

Not all of the options available during scripting appear to be configurable from Tools > Options.
I'm running 9.00.3042.00. Is there a hotfix that remedies this? Or is the selective availability by design? I'm mainly interested in the "Script Drop" option.

Vivek