SQL Server - copy stored procedures from one db to another

Question

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

User · Answer

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

User · Answer

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

User · Answer

use   select   from sys procedures   to show all your procedures   sp helptext  objname    Procedure name    to get the code  and your creativity to build something to loop through them all and generate the export code

User · Answer

You can generate scriptof the stored proc s as depicted in other answers  Once the script have been generated  you can use sqlcmd to execute them against target DB like  sqlcmd -S  lt server name gt  -U  lt user name gt  -d  lt DB name gt  -i  lt script file gt  -o  lt output log file gt

User · Answer

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  get the sp and execute it

User · Answer

In Mgmt Studio  right-click on your original database then Tasks then Generate Scripts    - follow the wizard

User · Answer

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 P where P name not in    select name from DB2 sys procedures P2

User · Answer

Another option is to transfer stored procedures using SQL Server Integration Services  SSIS   There is a task called Transfer SQL Server Objects Task  You can use the task to transfer the following items    Tables Views Stored Procedures User-Defined Functions Defaults User-Defined Data Types Partition Functions Partition Schemes Schemas Assemblies User-Defined Aggregates User-Defined Types XML Schema Collection   It s a graphical tutorial for Transfer SQL Server Objects Task

User · Answer

You can use SSMS s  Generate Scripts     function to script out whatever you need to transfer   Right-click on the source database in SSMS  choose  Generate Scripts      and follow the wizard along   Then run your resultant script that will now contain the stored procedure create statements

User · Answer

I originally found this post looking for a solution to copying stored procedures from my remote production database to my local development database   After success using the suggested approach in this thread  I realized I grew increasingly lazy  or resourceful  whichever you prefer  and wanted this to be automated   I came across this link  which proved to be very helpful  thank you vincpa   and I extended upon it  resulting in the following file  schema backup ps1     server                servername   database              databaseName   output path           D  prod schema backup   login    username   password    password    schema                dbo   table path             output path table    storedProcs path       output path stp    views path             output path view    udfs path              output path udf    textCatalog path       output path fulltextcat    udtts path             output path udtt     System Reflection Assembly   LoadWithPartialName  Microsoft SqlServer ConnectionInfo      out-null  System Reflection Assembly   LoadWithPartialName  Microsoft SqlServer SMO     out-null  System Reflection Assembly   LoadWithPartialName  Microsoft SqlServer SmoExtended      out-null  srvConn   new-object Microsoft SqlServer Management Common ServerConnection  srvConn ServerInstance    server  srvConn LoginSecure    false  srvConn Login    login  srvConn Password    password  srv          New-Object Microsoft SqlServer Management SMO Server  srvConn   db           New-Object   Microsoft SqlServer Management SMO Database    tbl          New-Object   Microsoft SqlServer Management SMO Table    scripter     New-Object Microsoft SqlServer Management SMO Scripter  srvConn     Get the database and table objects  db    srv Databases  database    tbl               db tables   Where-object      schema -eq  schema  -and -not    IsSystemObject     storedProcs       db StoredProcedures   Where-object      schema -eq  schema -and -not    IsSystemObject     views             db Views   Where-object      schema -eq  schema     udfs              db UserDefinedFunctions   Where-object      schema -eq  schema -and -not    IsSystemObject     catlog            db FullTextCatalogs  udtts             db UserDefinedTableTypes   Where-object      schema -eq  schema       Set scripter options to ensure only data is scripted  scripter Options ScriptSchema     true   scripter Options ScriptData       false    Exclude GOs after every line  scripter Options NoCommandTerminator      false   scripter Options ToFileOnly               true  scripter Options AllowSystemObjects       false  scripter Options Permissions              true  scripter Options DriAllConstraints        true  scripter Options SchemaQualify            true  scripter Options AnsiFile                 true   scripter Options SchemaQualifyForeignKeysReferences    true   scripter Options Indexes                  true  scripter Options DriIndexes               true  scripter Options DriClustered             true  scripter Options DriNonClustered          true  scripter Options NonClusteredIndexes      true  scripter Options ClusteredIndexes         true  scripter Options FullTextIndexes          true   scripter Options EnforceScriptingOptions      true  function CopyObjectsToFiles  objects   outDir         clear out before      Remove-Item  outDir  -Force -Recurse     if  -not  Test-Path  outDir              System IO Directory   CreateDirectory  outDir                foreach   o in  objects              if   o -ne  null                  schemaPrefix                   if   o Schema -ne  null -and  o Schema -ne                        schemaPrefix    o Schema                                   removed the next line so I can use the filename to drop the stored proc               on the destination and recreate it               scripter Options FileName    outDir    schemaPrefix    o Name     sql               scripter Options FileName    outDir    schemaPrefix    o Name             Write-Host  Writing    scripter Options FileName              scripter EnumScript  o                       Output the scripts CopyObjectsToFiles  tbl  table path CopyObjectsToFiles  storedProcs  storedProcs path CopyObjectsToFiles  views  views path CopyObjectsToFiles  catlog  textCatalog path CopyObjectsToFiles  udtts  udtts path CopyObjectsToFiles  udfs  udfs path  Write-Host  Finished at   Get-Date   srv ConnectionContext Disconnect     I have a  bat file that calls this  and is called from Task Scheduler   After the call to the Powershell file  I have   for  f  f in   dir  b d  prod schema backup stp    do sqlcmd  S localhost  d dest db  Q  DROP PROCEDURE  f    That line will go thru the directory and drop the procedures it is going to recreate   If this wasn t a development environment  I would not like programmatically dropping procedures this way   I then rename all the stored procedure files to have  sql   powershell Dir d  prod schema backup stp    Rename-Item -NewName      name     sql      And then run   for  f  f in   dir  b d  prod schema backup stp    do sqlcmd  S localhost  d dest db  E  i   f  sql   And that iterates through all the  sql files and recreates the stored procedures   I hope that any part of this will prove to be helpful to someone

[sql] SQL Server - copy stored procedures from one db to another

Examples related to sql

Examples related to sql-server-2008

Examples related to copy

Examples related to procedures