SQL Server - 将存储过程从一个数据库复制到另一个数据库

Question

我对SQL是新手，需要将两个.mdf数据库合并为一个。我使用 SQL Server 2008 Manager - Tasks > Import/Export tables 完成了该操作。表和视图已成功复制，但新数据库中没有存储过程。有没有办法完成这个操作？

- Oak

---

如果您想以编程方式复制它们，请从这里开始：http://stackoverflow.com/a/6124487/138938 - Jon Crowell

Answer 1

• 右键单击数据库
• 任务
• 生成脚本
• 选择要脚本化的对象
• 脚本到文件
• 运行生成的脚本到目标数据库

- Jaimal Chohan

---

@BarryKaye 如果他有30-40个存储过程，右键单击不会有点慢吗？ - rvphx

哇，现在我想我不是唯一一个喜欢基于GUI的方法的人了！！ - rvphx

@RajivVarma - 你只需要为数据库执行一次此任务，而不是每个存储过程都执行一遍！如果你在“存储过程”旁边的顶层复选框上打勾，它会将它们全部选择 - 只需点击一次即可。 - Barry Kaye

当我在SSMS中打开文件时，它显示一个错误：“数据库'E：\ SQLServerEnterpriceRoot \ MSSQL10.MSSQLSERVER \ MSSQL \ DATA \ Ubelo.mdf'不存在。确保名称输入正确。当我尝试重新输入名称时，它会显示现有数据库列表。当我选择正确的数据库时，它将第一行更改为“USE [Ubelo] .MSSQLSERVER \ MSSQL \ DATA \ Ubelo”，并且“。”下划线（错误：附近的语法不正确）。有什么建议吗？谢谢 - Oak

好的，现在我明白了，只需要写成"USE [Ubelo]"，其他位置不会自动删除。感谢大家的帮助。 - Oak

Answer 2

这段代码将Master数据库中的所有存储过程复制到目标数据库，您可以通过在存储过程名称上进行筛选来选择要复制的存储过程。

@sql被定义为nvarchar（max），@Name是目标数据库。

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

- ShaQue

---

谢谢！在代码中没有声明但在注释中出现的是@sql和@Name：DECLARE @sql NVARCHAR(MAX); DECLARE @Name NVARCHAR(32); - datalifenyc

有没有办法在不同的服务器上执行相同的操作？从服务器A到服务器B？ - Rajaram1991

Answer 3

这里是一份列出了可以采取的不同方法并附带优缺点的清单：

使用SSMS生成脚本

• 优点：极易使用且默认支持
• 缺点：脚本可能不在正确的执行顺序中，如果存储过程已经存在于辅助数据库中，则可能会出现错误。请确保在执行之前查看脚本。

第三方工具

• 优点：像ApexSQL Diff这样的工具（这是我使用的工具，但还有许多其他供应商提供的工具，如Red Gate或Dev Art）将在一个点击中比较两个数据库并生成您可以立即执行的脚本。
• 缺点：这些工具并非免费（大多数供应商都有完全功能的试用版）。

系统视图

• 优点：您可以轻松查看辅助服务器上存在哪些存储过程，并仅生成您没有的那些存储过程。
• 缺点：需要更多的SQL知识。

以下是如何获取某个数据库中所有在另一个数据库中不存在的存储过程列表：

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

- LarryB

Answer 4

我最初是在寻找一种将存储过程从远程生产数据库复制到本地开发数据库的解决方案时发现了这篇文章。在成功使用本主题中建议的方法后，我意识到我变得越来越懒（或者说更具有资源性，无论您喜欢哪一个），并希望自动化此过程。我发现 this link 非常有帮助（感谢vincpa），并在此基础上进行了扩展，生成了以下文件（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()

我有一个批处理文件，调用了这个文件，并从任务计划程序中调用。在调用PowerShell文件之后，我有以下内容：

for /f %f in ('dir /b d:\prod_schema_backup\stp\') do sqlcmd /S localhost /d dest_db /Q "DROP PROCEDURE %f"

这行代码将遍历目录并删除要重新创建的存储过程。如果这不是开发环境，我不会喜欢以编程方式这样删除存储过程。然后，我将所有存储过程文件重命名为.sql：

powershell Dir d:\prod_schema_backup\stp\ | Rename-Item -NewName { $_.name + ".sql" }

然后运行：

for /f %f in ('dir /b d:\prod_schema_backup\stp\') do sqlcmd /S localhost /d dest_db /E /i "%f".sql

这会迭代所有的 .sql 文件并重新创建存储过程。我希望这其中的任何部分都能对某人有所帮助。

- vandsh

---

我很喜欢这个。我必须编写一个处理程序，每年从生产数据库中归档块。我不想让SQL文件挂起来，因为它们可能不会随着模式的发展而更新，所以我正在调整它，创建一个基于目标的空DB，而不需要将文件写入磁盘（更多要清理的东西）。我认为这可能是这个问题上最好和最可重复使用的答案，向您致敬！ - Steve Pettifer

Answer 5

使用

select * from sys.procedures

显示所有程序：

sp_helptext @objname = 'Procedure_name'

获取代码

并运用你的创造力构建一个循环，生成导出代码 :)

- Diego

Answer 6

您可以使用SSMS的“生成脚本…”功能来编写需要转移的内容。在SSMS中右键单击源数据库，选择“生成脚本…”，并按照向导进行操作。然后运行您的结果脚本，其中将包含存储过程创建语句。请注意保留HTML标记。

- bluevector

Answer 7

您可以生成存储过程的脚本，正如其他答案中所描述的那样。一旦生成了脚本，您就可以使用 sqlcmd 对目标数据库执行它们。

sqlcmd -S <server name> -U <user name> -d <DB name> -i <script file> -o <output log file> 

- Rahul

Answer 8

另一个选项是使用SQL Server Integration Services（SSIS）来转移存储过程。有一个名为“Transfer SQL Server Objects Task”的任务。您可以使用该任务来传输以下项目：

- 表 - 视图 - 存储过程 - 用户定义的函数 - 默认值 - 用户定义的数据类型 - 分区函数 - 分区方案 - 模式 - 组件 - 用户定义的聚合函数 - 用户定义的类型 - XML模式集合

这是一个关于Transfer SQL Server Objects Task的图形教程。

- Abdollah

Answer 9

-- This program copies (CREATE OR ALTER) a single PROCEDURE from one database to another
declare @SourceDatabase nvarchar(50);
declare @SourceSchemaName nvarchar(50)
declare @TargetDatabase nvarchar(50);
declare @ProceduresName nvarchar(50);
declare @sql nvarchar(max) 


 
set @SourceDatabase =   N'Northwind'        -- The name of the source database
set @SourceSchemaName = N'dbo'              -- The name of Procedure  SCHEME
set @ProceduresName =   N'CustOrderHist'    -- The name of Procedure   
set @TargetDatabase =   N'AdventureWorks'   -- The name of the Target database
-- -------- - - -  
 
-- If the PROCEDURE SCHEME does not exist, create it
set @sql = ' use [' +@TargetDatabase +'] ' +
            ' IF NOT EXISTS (SELECT * FROM sys.schemas WHERE name = ''' + @SourceSchemaName+''') '+
            ' BEGIN ' +
            '   EXEC('' CREATE SCHEMA '+ @SourceSchemaName +''') ' +
            ' END'
 
exec (@sql);

set @sql = ''
-- 
set @sql = @sql + ' use [' + @TargetDatabase +'] ;' +
                    ' declare @sql2 nvarchar(max) ;' + 
                    ' SELECT @sql2 = coalesce(@sql2,'';'' ) + [definition] + '' ; '' ' +
                    ' FROM  ['+@sourceDatabase+'].[sys].[procedures] p '  +
                    ' INNER JOIN ['+@sourceDatabase+'].sys.sql_modules m ON p.object_id = m.object_id '+
                    ' where SCHEMA_NAME([schema_id]) = ''' +@SourceSchemaName +''' and [name] = N''' + @ProceduresName + '''  ; ' +
                    ' set @sql2 = replace(@sql2,''CREATE PROCEDURE'',''CREATE OR ALTER PROCEDURE'')' +
                    ' exec (@sql2)'
exec (@sql)

- Dror Cohen

Answer 10

从MyDatabase.sys.sql_modules s INNER JOIN MyDatabase.sys.procedures p ON [s].[object_id] = [p].[object_id] WHERE p.name LIKE 'Something%'"中选择definition + char(13) + 'GO'，并将其输出到"c:\SP_scripts.sql -S MyInstance -T -t -w"。

获取存储过程并执行它。

- ShaQue

---

这是一个非常好的解决方案，但是1）您应该指出需要文本或文件输出（不要在网格中显示结果，否则将丢失EOL字符），2）在SQL Server Management Studio中似乎存在8k的文本输出限制。 - DAB