SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Other SQL Server Topics (2005)
 Generate SQL script - Stored Procedure
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

knockyo
Yak Posting Veteran

83 Posts

Posted - 01/31/2007 :  02:48:00  Show Profile  Reply with Quote
In SQL 2000 version, we can select all the Stored Procedure function and export out all the script.

But in 2005 version, how it can be done?

Thanks

SwePeso
Patron Saint of Lost Yaks

Sweden
30241 Posts

Posted - 01/31/2007 :  02:58:33  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Same thing.
Right click the database in mind and select TASKS -> GENERATE SCRIPT


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17627 Posts

Posted - 01/31/2007 :  03:01:27  Show Profile  Reply with Quote
using SSMS, highlight and right click on your stored procedure. choose 'Script Stored Procedure as' - 'CREATE TO'


KH


Edited by - khtan on 01/31/2007 03:01:51
Go to Top of Page

knockyo
Yak Posting Veteran

83 Posts

Posted - 01/31/2007 :  03:48:12  Show Profile  Reply with Quote
quote:
Originally posted by khtan

using SSMS, highlight and right click on your stored procedure. choose 'Script Stored Procedure as' - 'CREATE TO'


KH





but in this case, i need select one-by-one, right?

how can last time 2000version, Select ALL?
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30241 Posts

Posted - 01/31/2007 :  03:50:52  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Did you miss the "SELECT ALL" button?


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

knockyo
Yak Posting Veteran

83 Posts

Posted - 01/31/2007 :  03:58:07  Show Profile  Reply with Quote
In the interface i cannot find select all


i just want like last time 2000 version, after select all the store procedure, compile into sql script. After i re-open the sql script, it can replace my current stored procedure will the latest store procedure.

But in 2005 version, how it can be done?

thanks your all guide!
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30241 Posts

Posted - 01/31/2007 :  04:02:49  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Well, that is because you did not read what I suggested to you earlier.
I wrote that you should right click the DATABASE in mind, not the stored procedure.

SSMS is much more precise with that than EM is.



Peter Larsson
Helsingborg, Sweden
Go to Top of Page

knockyo
Yak Posting Veteran

83 Posts

Posted - 01/31/2007 :  04:13:33  Show Profile  Reply with Quote
quote:
Originally posted by Peso

Well, that is because you did not read what I suggested to you earlier.
I wrote that you should right click the DATABASE in mind, not the stored procedure.

SSMS is much more precise with that than EM is.



Peter Larsson
Helsingborg, Sweden



I see Peso. I thought generate the script is only for database. So, i don't know that can also can select multiple object types.

hehe....sorry Peso

between, mind to tell me what is SSMS and EM you mention above?
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30241 Posts

Posted - 01/31/2007 :  04:20:06  Show Profile  Visit SwePeso's Homepage  Reply with Quote
SSMS is SQL Server Management Studio
EM is Enterprise Manager


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

knockyo
Yak Posting Veteran

83 Posts

Posted - 01/31/2007 :  04:24:51  Show Profile  Reply with Quote
okay, thanks peso!
Go to Top of Page

martinkwong
Starting Member

Hong Kong
1 Posts

Posted - 02/06/2007 :  21:00:31  Show Profile  Visit martinkwong's Homepage  Reply with Quote
well..the problem to this method is that it will generate all the stored procedures in a single file..where as in SQL server 2000, if u select all stored procedures and generate SQL files, it will generate a file for each stored procedures. this makes it simple for ppl who use winmerge or any compare program to compare their particular stored procedures with their different versions...but i would say more or less its the same..:P
Go to Top of Page

jezemine
Flowing Fount of Yak Knowledge

USA
2886 Posts

Posted - 03/01/2007 :  23:06:38  Show Profile  Visit jezemine's Homepage  Reply with Quote
you can also use this:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=7388


www.elsasoft.org
Go to Top of Page

cclambie
Starting Member

Australia
2 Posts

Posted - 03/19/2009 :  21:04:49  Show Profile  Reply with Quote
Hi there guys,
I know this is an old post, but I have just tried the create scripts method on the Database in SSMS, and it isn't quite the same as the EM.
In EM, I could select all my SPs and "create scripts" which would write out
IF Exists
DROP
CREATE

Which was great when doing enmass updates from Development to Testing to Live environments.

How do you get the DROP inside the IN EXISTS?
SSMS writes
IF NOT EXISTS
CREATE
Or just
CREATE if the "IF NOT EXISTS" setting is False

Thoughts?

Go to Top of Page

cclambie
Starting Member

Australia
2 Posts

Posted - 03/23/2009 :  03:43:59  Show Profile  Reply with Quote
As per solution on EE
http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/Q_24254524.html#a23955373

Download Microsoft Database Publishing Wizard.
Is essentially the "Generate Scripts" but allows for DROP if EXISTS.



Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000