SQL Server-한 db에서 다른 db로 저장 프로 시저 복사

SQL Server-한 db에서 다른 db로 저장 프로 시저 복사

---

저는 SQL을 처음 접했고 2 개의 .mdf 데이터베이스를 하나로 결합해야했습니다. SQL Server 2008 관리자-작업> 테이블 가져 오기 / 내보내기를 사용하여 수행했습니다. 테이블과 뷰가 성공적으로 복사되었지만 새 데이터베이스에는 저장 프로 시저가 없습니다. 그렇게 할 방법이 있습니까?

---

• 데이터베이스를 마우스 오른쪽 버튼으로 클릭하십시오.
• 과제
• 스크립트 생성
• 스크립팅 할 개체를 선택하십시오.
• 파일에 스크립트
• 대상 데이터베이스에 대해 생성 된 스크립트 실행

---

이 코드는 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

---

늦었지만 유용한 정보를 더 많이 제공합니다.

다음은 장단점으로 할 수있는 일 목록입니다.

SSMS를 사용하여 스크립트 생성

• 장점 : 매우 사용하기 쉽고 기본적으로 지원됨
• 단점 : 스크립트가 올바른 실행 순서가 아닐 수 있으며 보조 데이터베이스에 저장 프로 시저가 이미있는 경우 오류가 발생할 수 있습니다. 실행하기 전에 스크립트를 검토하십시오.

타사 도구

• 장점 : 같은 도구 ApexSQL DIFF (이것은 내가 무엇을 사용하지만, 레드 게이트 또는 데브 예술의 도구 같은 많은 다른 사람들이있다) 한 번의 클릭으로 두 개의 데이터베이스를 비교하고 즉시 실행할 수있는 스크립트를 생성합니다
• 단점 : 이것들은 무료가 아닙니다 (대부분의 공급 업체는 완전한 기능의 평가판을 가지고 있습니다)

시스템보기

• 장점 : 보조 서버에 어떤 저장 프로 시저가 있는지 쉽게 확인할 수 있으며 가지고 있지 않은 프로 시저 만 생성 할 수 있습니다.
• 단점 : 더 많은 SQL 지식이 필요합니다.

다음은 다른 데이터베이스에 존재하지 않는 일부 데이터베이스의 모든 프로 시저 목록을 가져 오는 방법입니다.

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

---

원래이 게시물은 원격 프로덕션 데이터베이스에서 로컬 개발 데이터베이스로 저장 프로 시저를 복사하는 솔루션을 찾고있었습니다. 이 스레드에서 제안 된 접근 방식을 사용하여 성공한 후 저는 점점 게으르고 (또는 원하는대로 수완이 풍부 해져) 이것이 자동화되기를 원했습니다. 내가 건너 온 이 링크 매우 도움이 될 입증, (당신 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()

이것을 호출하는 .bat 파일이 있으며 작업 스케줄러에서 호출됩니다. Powershell 파일에 대한 호출 후 다음이 있습니다.

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.

---

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.

---

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 :)

---

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 <server name> -U <user name> -d <DB name> -i <script file> -o <output log file> 

---

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

---

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

참고URL : https://stackoverflow.com/questions/10703424/sql-server-copy-stored-procedures-from-one-db-to-another