Server Fault is a question and answer site for system and network administrators. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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

share|improve this question
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
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

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.


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

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

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

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

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

@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

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


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

Your Answer


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.