Script stored procedure as... Setting the create to include the drop command

Hi experts,
I'm using SQL Server 2005 Management Studio Express, when I want to script out a stored procedure I want it to include the drop if exists command.  I remember doing this on SQL Server 2000 but I cannot figure out where I set the option in SQL Server 2005 Express.  I found an article on what the options are but I am unable to get to that configuration.  Is it not available in Express?

Here is the article I am referring to:
http://msdn.microsoft.com/en-us/library/ms186472%28SQL.90%29.aspx
http://weblogs.sqlteam.com/tarad/archive/2006/09/20/12374.aspx
prgManAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
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.

Shannon_LowderCommented:
I take it in your object list, when you right click on the stored Procedure and choose script, you don't have "DROP and CREATE to".

In that case, All I can think of is right click, script "DROP to clipboard", then paste that to your query, then right click, script "CREATE to clipboard" then past that... that would give you both.

Unfortunately not in one shot.

---
Shannon Lowder
Database Engineer
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
Aneesh RetnakaranDatabase AdministratorCommented:
right click the object -> script stored procedure as -> drop to  -> new window

right click the object -> script stored procedure as -> Create to  -> clipboad and paste it on the other window
0
prgManAuthor Commented:
Hi guys,
Thanks for the quick replies.  You are both right about how it can be done manually.  What I was wondering if there was Optional setting/configuration for SQL Server that would tell it to include the drop when CREATE TO is selected.

If you look at the article:
http://weblogs.sqlteam.com/tarad/archive/2006/09/20/12374.aspx

The author mentions that it should be configurable in a particular dialog box (he shows a pic of the dialog box).  What I am looking for are the steps to get to that dialog box.  The article is written back in 2006 so I am hoping that MS corrected the original issue he mentioned.  

Thanks again!

0
Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

Shannon_LowderCommented:
According to the article if you right click on the database, choose tasks, then generate scripts, you should get that dialog. To get to the options, you'll have to choose a database, then hit next.

The problem is, that will generate a script that could drop and create ALL the objects.  

This feature works in my copy of 2005 and 2008 developer.  I cannot confirm or deny it exists for Express.  

---
Shannon Lowder
Database Engineer
0
prgManAuthor Commented:
Ah, I see it.  So the dialog box appears when you do it from the database level.  I do see a checkbox that you can select or not select all objects to be impacted.  Later it lets you pick each object.  But as the article states it only allows you to Drop or Add instead of both despite what the label says below it.  

So unfortunately it appears that their is no setting to include drop automatically with the create.  So your previous recommendation is the easiest way to accomplish it.

Thanks
0
prgManAuthor Commented:
Good answer to an oversight on MS part.
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 2005

From novice to tech pro — start learning today.