4

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

Improve this question

7 Answers 7

Reset to default
8

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.

Improve this answer
3
  • 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, 2009 at 14:44
  • +1 for step by step instructions.
    – mrdenny
    Jul 16, 2009 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, 2012 at 21:23
5

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

Improve this answer
1
  • 1
    is there an import/export wizard in 2008? 2k had the option to copy objects.
    – jhayes
    Jul 17, 2009 at 1:42
3

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

SELECT ROUTINE_DEFINITION
FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_TYPE='PROCEDURE'
Improve this answer
2
  • 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, 2009 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, 2012 at 8:28
1

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.

Improve this answer
1

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

Improve this answer
1

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.

Improve this answer
1

@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

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .