No Answer for Copy Stored Procedure one Database to another

Question

I want to copy my stored procedure from one database to another in programatical way I tried with many ways it is not possible , iam tried using the following command

exec(string Sql)

but some sp are executed in this way but some says in correct syntax near... What is the best way to do this...

Iam using sql server 2005

can anyone help me in this

You can do it programatically in .NET using the SMO framework. — vhadalgi, Oct 28 '13 at 09:32
possible duplicate of [SQL: programmatically copy stored procedures from one db to another](http://stackoverflow.com/questions/6124025/sql-programmatically-copy-stored-procedures-from-one-db-to-another) — huMpty duMpty, Oct 28 '13 at 10:40

score 1 · Accepted Answer · edited May 23 '17 at 12:10

Here is code that copies all sp's in the Master database to the target database, you can copy just the sp's you like by filtering the query on procedure name.

@sql is defined as nvarchar(max) @Name is the target database

DECLARE c CURSOR FOR 
   SELECT Definition
   FROM [ResiDazeMaster].[sys].[procedures] p
   INNER JOIN [ResiDazeMaster].sys.sql_modules m ON p.object_id = m.object_id

OPEN c

FETCH NEXT FROM c INTO @sql

WHILE @@FETCH_STATUS = 0 
BEGIN
   SET @sql = REPLACE(@sql,'''','''''')
   SET @sql = 'USE [' + @Name + ']; EXEC(''' + @sql + ''')'

   EXEC(@sql)

   FETCH NEXT FROM c INTO @sql
END             

CLOSE c
DEALLOCATE c

If you want to use the sql server tool, then follow SQL Server - copy stored procedures from one db to another

:I Followed your way but some sp says Unclosed quotation mark after the character string ' what i can do for this — ShaQue, Oct 28 '13 at 11:48

No Answer for Copy Stored Procedure one Database to another

1 Answers1

Linked