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 2000 Forums
 SQL Server Development (2000)
 generatre create scripts for predefined queries
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

scabral7
Yak Posting Veteran

57 Posts

Posted - 05/13/2010 :  09:35:05  Show Profile  Reply with Quote
Hi,

I have about 800 stored procedures in my sql 2000 database. I want to move about 350 of them over to a new SQL 2005 database.

I know you can right click on the stored proc and say generate sql script, however i didn't want to have to sift through and manually pick out the 350 procs that i need.

i have the 350 proc names stored in a table. I was wondering if there was a way using script to use that table with the proc names and generate the create scripts that way. i would also need to script out the permssions for each proc.

Any ideas?

Kristen
Test

United Kingdom
22431 Posts

Posted - 05/13/2010 :  09:47:22  Show Profile  Reply with Quote
Can't think of one, sorry.

FWIW we store all Sproc source code in files (one per object). They are stored in a revision control system, and it makes it much easier to solve questions like the one you are asking - rather than updating code directly in database and then trying to synchronise it to make update-version, or extract code for specific modules etc. (we use one sub-directory per module for example)

No help with where you are now, sorry about that, but maybe something to consider.

You'll probably tell me know that you normally work like that but this is some 3rd party database that you've been given to sort out?!
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22772 Posts

Posted - 05/13/2010 :  09:51:10  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
You may need to play with this and let mw know if it helps
http://beyondrelational.com/blogs/madhivanan/archive/2009/10/26/script-out-procedures-to-seperate-files.aspx

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

Kristen
Test

United Kingdom
22431 Posts

Posted - 05/13/2010 :  10:29:52  Show Profile  Reply with Quote
Ah ... does SSMS still allow script-each-object-to-separate-file?

If so you could script all objects like that, and then use a Batch File (constructed from your table of required names) to MOVE the relevant flies to a different folder - then use them for your script.
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22772 Posts

Posted - 05/13/2010 :  10:37:06  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
<<
Ah ... does SSMS still allow script-each-object-to-separate-file?
>>

Yes. It allows. There is an option file per object

Madhivanan

Failing to plan is Planning to fail
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.2 seconds. Powered By: Snitz Forums 2000