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.
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?!
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.