Database Stored Procedure moving issues

Question

I want to move all the stored procedures from an old database to a new database through SQL queries.

I have created the Database through a Dynamic stored procedure & it has worked fine. I have moved all the tables as well but I am not able to move the stored procedures to the new database.

I tried pasting SQL statements into a table but i don't know to execute them.

Is there an easy way to migrate my stored procedures?

I got all the stored procedures which are created in old database by the following query

Select Name from '+@OldDatabse+'.sys.procedures

Can anyone help me?

score 0 · Answer 1 · answered Oct 24 '13 at 05:38

0

Link your old database server in your page

$old_host= "OLD DB IP";
$old_db = "OLD DB"

$new_host ="localhost";
$new_db= "new_db";

$old_con=mysql_connect($old_host,$old_db);

mysql_select_db($old_db,$old_con);
mysql_select_db($new_db,$new_con);

answered Oct 24 '13 at 05:38

Mohatir S

5
3

:Thank you for response, i want in queries to generate Sp from old database to new database – ShaQue Oct 24 '13 at 05:48

Ramesh Rajendran · Answer 2 · 2013-10-24T09:22:26.987

You have do with this way

try this following

Right click on database >
Tasks >
Generate Scripts >
Select the objects you wish to script( Store-Procedure or table ) >
Script to File ( Where you got the script code details) >
Set schema or data or schema with data for Table (if you take a sp , then don't set anything)
Run generated scripts against target database .

If you need to move this by using query, then look at this link :

Copy all SP's from one Database to another Database using a script

Update:

...........

Here is a 4 option for move the Stored-procedure '

Option 1: Use the scripting wizard Right-click the db --> tasks --> Generate scripts --> go through the wizard.

Option 2: Open the stored procedures folder in SSMS (in the object explorer details window). (You can also press F7 to do so, see this blog for details). You can use shift+click to select all the stored procedures and you can then right-click and script them to a file.

Options 3: The simplest of them.

bcp "SELECT definition + char(13) + 'GO' FROM MyDatabase.sys.sql_modules s INNER JOIN MyDatabase.sys.procedures p ON [s].[object_id] = [p].[object_id] WHERE p.name LIKE 'Something%'" queryout "c:\SP_scripts.sql" -S MyInstance -T -t -w

Option 4 : To script All the Stored Procedures in the Database

SELECT    O.Name as ProcName 
        ,M.Definition as CreateScript 
        ,O.Create_Date 
        ,O.Modify_Date 
FROM sys.sql_modules as M INNER JOIN sys.objects as O 
ON M.object_id = O.object_id 
WHERE O.type = 'P'

If the Stored Procedure is created with ENCRYPTION option then you will get the NULL in the definition column.

:i tried your method but how to execute the content of an sp in new database, I am not clear about that can you please explain me on that...? — ShaQue, Oct 24 '13 at 09:48

score 0 · Answer 3 · edited May 23 '17 at 10:25

0

Possible duplicates of below link:

SQL Server - copy stored procedures from one db to another

Right click on database
Tasks
Generate Scripts
Select the objects you wish to script
Script to File
Run generated scripts against target database

I hope it will help you.. :)

edited May 23 '17 at 10:25

Community

1
1

answered Oct 24 '13 at 06:03

Hitesh

3,508
1
18
24

Database Stored Procedure moving issues

3 Answers3