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?
 
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
 
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
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.