How do I copy a stored proc to another database

[smsinger3]

Hello I would like to copy several stored procedures from one database to another, using T-SQL. I will run this periodically to refresh my current database.

I don't want to script the individual stored procedures because they change periodically and I would have to re-script it every time I copy them. I would also prefer not to use replication at this point.

Any ideas how quickly write a T-SQL statement in query analyzer?

Thank you for your help in advance,

Steve S
sms@hmbnet.com

 

[tlbroadbent]

Open the stored procedure. Copy the code to the clipboard. Open Query Analyzer in the other database. Paste the code and execute it.

Terry
------------------------------------
Blessed is the man who, having nothing to say, abstains from giving us worthy evidence of the fact. -George Eliot

 

[smsinger3]

Thanks Terry. However, I will be doing this to the same stored procedurs several times a month. I am trying to avoid the "manual" cut/paste method. Instead I would like to create some sql statements that that I can save and just run those whenever I need to. Any other suggestions?

 

[tlbroadbent]

I'm struggling to understand your requirement. Must be the lack of sleep! ;-)

When you change the SPs, is the structure, function or data handled changing? What kind of changes do you make? Would the changes be better handled by parameterizing the SPs? Or wouldn't it be better to create system stored procedures that could be executed in any database you chose? Terry
------------------------------------
Blessed is the man who, having nothing to say, abstains from giving us worthy evidence of the fact. -George Eliot

 

[smsinger3]

Basically, I'm copying the production stored procedures to my test database. I don't know which ones change, so I copy all of them. I don't want to use replication because I don't want to touch the production server for my testing purposes. There are times where I may want to change the stored procedure for my testing, so we really can't share them. Make sense? (maybe I need more caffeine, too!?)

 

[balor]

I think the easiest way to keep a test db updated is to take a backup on the production db and do a restore from that backup on the test db. Then you know that the production and the test db is identical.