How to store S.P scripts in a folder?

Hi

I'm using Microsoft SQL server management studio express 2005.

I’ve a Database with some S.Ps.

How to store those S.P scripts in a folder?

Please advice

Thanks

They will all show up in the GUI under Programmability/Stored Procedures, though you can't actually create custom folders for stored procedures in SSMS although its been put forward as a suggestion for SQL2008.

If you want your sprocs well organised and shared with other people, you may want to consider a source control product such as sourcesafe or clearcase.

HTH!

|||

I know that it will show up in GUI under Programmability/S.P

my question is

I need to store the scripts of all those S.Ps in a Folder created on my Desktop

how?

please advise

Thanks

|||

Sorry, i misunderstood your question.

It seems the ability to script out objects in one easy hit has been missed out of SSMS. I would therefore suggest spending some time writing a little SMO script which you can use to script objects to file. Otherwise, its a case of doing each one individually i think.

Do some research into SMO for details on how to program with it.

HTH

|||

Hi vijay,

following the step as below:

1. Open SSMS

2. Login SQL Server

3. Make sure you can see the Database that you wanna Script SP.

4. Expand the node to Programmability \ Stored Procedure , then stay the focus on it.

5. After Step 4 you can see SPs list in the right-side window "Summary".

6.Click the first SP , press "Shift" without release.

7.choose the last one SP.

8.punch the mouse 's Right button ,choose the " Script Stored Procedure as →" "Create to →" to Flie.

9.Choose the folder that you wanna store those SPs and press "Save".

10. done.

Hoping it will help u.

Best Regrades,

Hunt.

|||

1.You can generate a script and save manually to a folder.

Thanks

Databasetimes.net

|||

Thanks Tsai

I followed these steps.

It’s creating one file with all S.P scripts.

Can’t we create a separate file for each & every S.P?

Please advice

Thanks

|||You have to write your own SMO script for this. Its simple; you can do it on your own favorite language(on .NET/C#/VB)

|||

Thanks Mani

I’m very much appreciate if you could provide that SMO script in C#

Thanks

Vijay

|||

hi vijay,

ok,I got your issue.

u don't have to make a SMO.

We can use SSMS DataBase Generate Script Wizard.

1.open SSMS

2.Login SQLServer

3.choose the Databse that you wanna Gen SPs, Stay Focus on the it.

4.With punch mouse Right button → Task → Generate Script

5.Generate Script Wizard will pop up → Choose "Next"

6.make sure Step "Select a DataBase" → that you select at step 3. and Choose "Next"

7.Step "Choose Script Option" → Choose "Next"

if you wanna drop SP before Re-Create SP,u have to Choose "Script Create" = True , "Script Drop" = True

8.Step "Choose Object Types" → Select "Stored Procedure" → Choose "Next"

9.Step "Choose Stored Procedure" → Click "Select All" → Choose "Next"

10.Step "Output Option" → Select " Script to File \ File to generate \ File per Object " → Choose "Next "

11 Step"Script Wizard Summary" → Choose "Finish"

12. Done.

Hoping this can help u.

Best Regrades,

Hunt.

|||

Vijay,

This problem has been fixed in SP2. Earlier to sp2 yes it was not possible to script objects in seperate file. Upgrade the system to SP2 and then try. you will be able to script each object in seperate file

Download sp to from here

http://support.microsoft.com/kb/913089/

Madhu

|||

Thanks a lot Tsai

I got my answer from your nice reply. This is what I'm expecting.

I marked it as Answer.

With Regards

Vijay

How to store S.P scripts in a folder?

Hi

I'm using Microsoft SQL server management studio express 2005.

I’ve a Database with some S.Ps.

How to store those S.P scripts in a folder?

Please advice

Thanks

They will all show up in the GUI under Programmability/Stored Procedures, though you can't actually create custom folders for stored procedures in SSMS although its been put forward as a suggestion for SQL2008.

If you want your sprocs well organised and shared with other people, you may want to consider a source control product such as sourcesafe or clearcase.

HTH!

|||

I know that it will show up in GUI under Programmability/S.P

my question is

I need to store the scripts of all those S.Ps in a Folder created on my Desktop

how?

please advise

Thanks

|||

Sorry, i misunderstood your question.

It seems the ability to script out objects in one easy hit has been missed out of SSMS. I would therefore suggest spending some time writing a little SMO script which you can use to script objects to file. Otherwise, its a case of doing each one individually i think.

Do some research into SMO for details on how to program with it.

HTH

|||

Hi vijay,

following the step as below:

1. Open SSMS

2. Login SQL Server

3. Make sure you can see the Database that you wanna Script SP.

4. Expand the node to Programmability \ Stored Procedure , then stay the focus on it.

5. After Step 4 you can see SPs list in the right-side window "Summary".

6.Click the first SP , press "Shift" without release.

7.choose the last one SP.

8.punch the mouse 's Right button ,choose the " Script Stored Procedure as →" "Create to →" to Flie.

9.Choose the folder that you wanna store those SPs and press "Save".

10. done.

Hoping it will help u.

Best Regrades,

Hunt.

|||

1.You can generate a script and save manually to a folder.

Thanks

Databasetimes.net

|||

Thanks Tsai

I followed these steps.

It’s creating one file with all S.P scripts.

Can’t we create a separate file for each & every S.P?

Please advice

Thanks

|||You have to write your own SMO script for this. Its simple; you can do it on your own favorite language(on .NET/C#/VB)

|||

Thanks Mani

I’m very much appreciate if you could provide that SMO script in C#

Thanks

Vijay

|||

hi vijay,

ok,I got your issue.

u don't have to make a SMO.

We can use SSMS DataBase Generate Script Wizard.

1.open SSMS

2.Login SQLServer

3.choose the Databse that you wanna Gen SPs, Stay Focus on the it.

4.With punch mouse Right button → Task → Generate Script

5.Generate Script Wizard will pop up → Choose "Next"

6.make sure Step "Select a DataBase" → that you select at step 3. and Choose "Next"

7.Step "Choose Script Option" → Choose "Next"

if you wanna drop SP before Re-Create SP,u have to Choose "Script Create" = True , "Script Drop" = True

8.Step "Choose Object Types" → Select "Stored Procedure" → Choose "Next"

9.Step "Choose Stored Procedure" → Click "Select All" → Choose "Next"

10.Step "Output Option" → Select " Script to File \ File to generate \ File per Object " → Choose "Next "

11 Step"Script Wizard Summary" → Choose "Finish"

12. Done.

Hoping this can help u.

Best Regrades,

Hunt.

|||

Vijay,

This problem has been fixed in SP2. Earlier to sp2 yes it was not possible to script objects in seperate file. Upgrade the system to SP2 and then try. you will be able to script each object in seperate file

Download sp to from here

http://support.microsoft.com/kb/913089/

Madhu

|||

Thanks a lot Tsai

I got my answer from your nice reply. This is what I'm expecting.

I marked it as Answer.

With Regards

Vijay

How to stop PAD_INDEX being scripted

Our team checks SQL scripts into version control. On my machine, however, the generated script always differs slightly. When I generate a create table script, the primary key part always has PAD_INDEX = OFF. Nobody else's server generates this. Does anyone know what option or setting I need to change to prevent PAD_INDEX = OFF from being output?

WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]

BOL specifies

When creating indexed views or manipulating rows in tables participating in an indexed view, seven SET options must be assigned specific values. The SET options ARITHABORT, CONCAT_NULL_YIELDS_NULL, QUOTED_IDENTIFIER, ANSI_NULLS, ANSI_PADDING, and ANSI_WARNINGS must be ON. The SET option NUMERIC_ROUNDABORT must be OFF.

If any of these settings is different, data modification statements (INSERT, UPDATE, DELETE) on any table referenced by an indexed view fail and SQL Server raises an error listing all SET options that violate setting requirements. In addition, for a SELECT statement that involves an indexed view, if the values of any of the SET options are not the required values, SQL Server processes the SELECT without considering the indexed view substitution. This ensures correctness of query result in cases where it can be affected by the above SET options.

If the application uses a DB-Library connection, all seven SET options on the server must be assigned the required values. (By default, OLE DB and ODBC connections have set all of the required SET options correctly, except for ARITHABORT.)

|||

Thank you for taking the time to copy and paste. However, I don't see how your post is relevant.

I am asking what determines whether the PAD_INDEX option is included in scripted CREATE TABLE statements.

|||Is anyone able to help with this?