SQL Server - copy stored procedures from one db to another

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)

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.

• 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_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