Copying Stored Procedures SQL 2000

[azagthoth]

Hello,

Is there an easy way of copying all Stored Procedures from one database to another?

I have tried doing this through DTS by selecting only stored procedures and omitting all tables & data, but I find that all tables in the destination database are all blank after DTS has completed (thank God I backed it up!).

Can anyone help?

[techgnome]

There are a couple ways of doing this.
One if DTS - for the tables, make sure you set the Copy Structure and Data setting.
Secondly is top detach the database, copy the MDF (NOT THE LDF) to a new location and rename it. Re-attach the original database, then in Query Analyzer, issue the following command sp_attach_single_file_db 'Name OF New DB Herer','FULL PATH TO THE MDF Here'
Note that the full path to the MDF should be local to the server (IE, it's C or D drive)
Thirdly, make a back up of the original database. Create a new database, Then right-click the new DB, select "All Tasks" -> "Restore From Back up".... now this is where it gets tricky.
1) Set the "from" option to "From Device" (there's three option buttons for this)
2) Select "Add"
3) Browse until you find the backup you made from the original DB
4) Back at the restore dialog box, select the second tab
5) Mark the option "Force Restore"
6) In the grid, change the paths and filenames to point to the MDF and LDF of the NEW database (they should have defaulted to the old one).
7) Click OK to begin the restore.
Done!

TG

[brucevde]

DTS has always worked for me...

There is an option to "include dependency objects", which by default is checked. That might have caused the tables to be included in the export even though they were not chosen.

The other choice is to generate a script for all stored procedures, save the script to a file and then execute the script in Query Analyzer.

[techgnome]

Never mind.... I see that all you want is just the stored procedures....
Not sure why it didn't work for you. Alternatively, you can script your stored procedures and then run the script in the second DB.

Tg