Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

SQL Server - copy stored procedures from one db to another

I am new to SQL, and what I needed to do was to combine 2 .mdf databases into one. I did that using SQL Server 2008 Manager - Tasks > Import/Export tables.The tables and views were copied successfully, but there are no Stored procedures in the new database. Is there any way to do that?

like image 711
Oak Avatar asked May 22 '12 13:05

Oak


People also ask

How do I copy a stored procedure from one database to another in SQL?

Launch MS SQL Server Management Studio in your system and go to the Object Explorer. Step 2. Right-click on the database from which you want to move data and then click on Tasks>>Generate Scripts… A Generate and Publish Scripts Wizard will appear on the screen, click on the Next button to proceed.


2 Answers

  • Right click on database
  • Tasks
  • Generate Scripts
  • Select the objects you wish to script
  • Script to File
  • Run generated scripts against target database
like image 121
Jaimal Chohan Avatar answered Sep 18 '22 09:09

Jaimal Chohan


This code copies all stored procedures in the Master database to the target database, you can copy just the procedures 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 
like image 30
ShaQue Avatar answered Sep 20 '22 09:09

ShaQue