Move stored procedures from one database to another in SQL Server

Question

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

Answer 1

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.

Answer 2

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

Answer 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'

Answer 4

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

Answer 5

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.

Answer 6

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.

Answer 7

@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