Generate SQL script - Stored Procedure

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Other SQL Server Topics (2005)

Generate SQL script - Stored Procedure

Author

Topic

knockyo
Yak Posting Veteran

83 Posts

Posted - 2007-01-31 : 02:48:00

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

30421 Posts

Posted - 2007-01-31 : 02:58:33

Same thing.
Right click the database in mind and select TASKS -> GENERATE SCRIPT

Peter Larsson
Helsingborg, Sweden

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-01-31 : 03:01:27

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

knockyo
Yak Posting Veteran

83 Posts

Posted - 2007-01-31 : 03:48:12

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?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-31 : 03:50:52

Did you miss the "SELECT ALL" button?

Peter Larsson
Helsingborg, Sweden

knockyo
Yak Posting Veteran

83 Posts

Posted - 2007-01-31 : 03:58:07

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!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-31 : 04:02:49

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

knockyo
Yak Posting Veteran

83 Posts

Posted - 2007-01-31 : 04:13:33

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?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-31 : 04:20:06

SSMS is SQL Server Management Studio
EM is Enterprise Manager

Peter Larsson
Helsingborg, Sweden

knockyo
Yak Posting Veteran

83 Posts

Posted - 2007-01-31 : 04:24:51

okay, thanks peso!

martinkwong
Starting Member

1 Post

Posted - 2007-02-06 : 21:00:31

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

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-03-01 : 23:06:38

you can also use this:

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

www.elsasoft.org

cclambie
Starting Member

2 Posts

Posted - 2009-03-19 : 21:04:49

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?

cclambie
Starting Member

2 Posts

Posted - 2009-03-23 : 03:43:59

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.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources