Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

I'm using SQL Server 2008, and I would like to copy stored procedures from one database to another. How?

share|improve this question
add comment

7 Answers

up vote 8 down vote accepted

Right click on the SP under the DB and click Script Stored Procedure As > CREATE To > File, it will create an SQL script file, then run that script on the other database.

share|improve this answer
1  
Just be careful, because this script often has a USE [Database] command at the top. If the new database is named something different, you'll want to update that as well. –  Joel Coel Jul 15 '09 at 14:44
    
That is true, thanks. –  DanBig Jul 15 '09 at 15:03
    
+1 for step by step instructions. –  mrdenny Jul 16 '09 at 23:37
    
If you use the object explorer you can select multiple stored procedures and have them created into one file / new query. –  m4tt1mus May 8 '12 at 21:23
add comment

Just use the Management Studio to generate a script for the stored procedures, save the script to a file then run it on the other SQL Server.

From memory you right click the database and under All Tasks is Generate Scripts or something like that. This will produce the Transact-SQL to create whatever you select.

JR

share|improve this answer
1  
is there an import/export wizard in 2008? 2k had the option to copy objects. –  jhayes Jul 17 '09 at 1:42
add comment

Here is a query (set output to text) to return the stored procedures :

SELECT ROUTINE_DEFINITION
FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_TYPE='PROCEDURE'
share|improve this answer
    
This doesn't always work, depending on text wrap settings, and the width of the code. It can under certain setups give you funky CRLFs in the output code, as can sp_helptext. –  mrdenny Jul 16 '09 at 23:38
    
The true (brain dead) limitation of ROUTINE_DEFINITION is that it truncates to nvarchar(4000) cf. msdn.microsoft.com/en-us/library/aa933197%28v=sql.80%29.aspx. The other problems mrdenny mentions are limitations of the GUIs like SSMS and do not apply if you query the database by ADO.NET –  bernd_k Jan 4 '12 at 8:28
add comment

The answers above are all good and will work. The issue is (in my world, anyway): Where are your sprocs?

In my case, we have one kit of sprocs in the app db (business logic, etc), and another set of system management sprocs in master.

The kicker for me is having to move (and keep in sync) the sprocs in master ....

share|improve this answer
add comment

You can Copy all database including Stored Prcedures, maybe this is what you need:

Into Ms SQL Server Managment studio -> Right Click into source data base, Go to "Tasks" menu and select "Copy Database".

This will crerate a full copey of the source includind tables, data and Stored Precedures.

share|improve this answer
add comment

@Ash Machine has a good idea, but he uses the wrong query, which limits the returned result to nvarchar(4000).

Use instead

Select definition from sys.sql_modules where object_id = object_id('<procname>')

and you get the full untruncated definition.

In most practical cases this works in GUIs like SSMS when you use output to text and set max characters pro column to 8192. If the some lines are wider than 8192 this fails. But note this is a limitation of the query tool and not of the query. This limitation made it way into SQLServer 2012 too.

Using ADO.NET perhaps via PowerShell as in SQLPSX sqlise you can get the complete definition.

If you only want to store backups of the procedure definition in some table of the same database it works as well

share|improve this answer
add comment

There are options in SSMS to make it generate permissions for users as well - that's handy.

Tools>options>scripting

You can also script multiple by clicking 'stored procedures' in your Object Explorer pane and then multi selecting procedures in the Object Explorer Details window. Right click the selected procedures and you're ready to go.

I prefer redgate's SQL Compare.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.