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

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_idOPEN cFETCH NEXT FROM c INTO @sqlWHILE @@FETCH_STATUS = 0 BEGIN   SET @sql = REPLACE(@sql,'''','''''')   SET @sql = 'USE [' + @Name + ']; EXEC(''' + @sql + ''')'   EXEC(@sql)   FETCH NEXT FROM c INTO @sqlEND             CLOSE cDEALLOCATE c

Late one but gives more details that might be useful…

Here is a list of things you can do with advantages and disadvantages

Generate scripts using SSMS

• Pros: extremely easy to use and supported by default
• Cons: scripts might not be in the correct execution order and you might get errors if stored procedure already exists on secondary database. Make sure you review the script before executing.

Third party tools

• Pros: tools such as ApexSQL Diff (this is what I use but there are many others like tools from Red Gate or Dev Art) will compare two databases in one click and generate script that you can execute immediately
• Cons: these are not free (most vendors have a fully functional trial though)

System Views

• Pros: You can easily see which stored procedures exist on secondary server and only generate those you don’t have.
• Cons: Requires a bit more SQL knowledge

Here is how to get a list of all procedures in some database that don’t exist in another database

select *from DB1.sys.procedures Pwhere P.name not in  (select name from DB2.sys.procedures P2)