salida - procedimiento almacenado sql server select

SQL Server: copie los procedimientos almacenados de un db a otro

Question

Soy nuevo en SQL, y lo que tenía que hacer era combinar 2 bases de datos .mdf en una sola. Lo hice usando el Administrador de SQL Server 2008 - Tareas> Importar / Exportar tablas. Las tablas y vistas se copiaron con éxito, pero no hay procedimientos almacenados en la nueva base de datos. ¿Hay alguna forma de hacer eso?

Answer 1

En Mgmt Studio, haga clic con el botón derecho en su base de datos original, luego Tareas y genere guiones ... - siga el asistente.

Answer 2

Este código copia todos los procedimientos almacenados en la base de datos maestra a la base de datos de destino, puede copiar solo los procedimientos que desee al filtrar la consulta sobre el nombre del procedimiento.

@sql se define como nvarchar (max), @Name es la base de datos de destino

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

Answer 3

Originalmente encontré esta publicación buscando una solución para copiar procedimientos almacenados desde mi base de datos de producción remota a mi base de datos de desarrollo local. Después del éxito con el enfoque sugerido en este hilo, me di cuenta de que me volvía cada vez más flojo (o ingenioso, según lo que prefiriera) y quería que esto se automatizara. Me encontré con este enlace , que resultó ser muy útil (gracias vincpa), y me extendí sobre él, dando como resultado el siguiente archivo (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()

Tengo un archivo .bat que llama esto, y se llama desde el Programador de tareas. Después de la llamada al archivo Powershell, tengo:

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

Esa línea irá a través del directorio y soltará los procedimientos que va a recrear. Si este no fuera un entorno de desarrollo, no me gustaría dejar caer los procedimientos de esta manera. Luego renombro todos los archivos de procedimiento almacenados para tener .sql:

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

Y luego ejecuta:

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

Y eso itera a través de todos los archivos .sql y recrea los procedimientos almacenados. Espero que cualquier parte de esto resulte útil para alguien.

Answer 4

Puede generar secuencias de comandos de los procesos almacenados como se muestra en otras respuestas. Una vez que se ha generado la secuencia de comandos, puede usar sqlcmd para ejecutarlos contra DB de destino como

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

Answer 5

Puede usar la función "Generar scripts ..." de SSMS para crear secuencias de comandos de todo lo que necesite transferir. Haga clic derecho en la base de datos de origen en SSMS, elija "Generar secuencias de comandos ..." y siga al asistente a lo largo. A continuación, ejecute la secuencia de comandos resultante que ahora contendrá el procedimiento almacenado crear instrucciones.

Answer 6

SELECCIONAR definición + char (13) + ''IR'' DESDE 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

obtener el sp y ejecutarlo

Answer 7

Uno más tarde, pero da más detalles que podrían ser útiles ...

Aquí hay una lista de cosas que puede hacer con ventajas y desventajas

Generar scripts usando SSMS

• Pros: extremadamente fácil de usar y compatible por defecto
• Contras: es posible que las secuencias de comandos no estén en el orden de ejecución correcto y es posible que obtenga errores si el procedimiento almacenado ya existe en la base de datos secundaria. Asegúrese de revisar el script antes de ejecutarlo.

Herramientas de terceros

• Pros: herramientas como ApexSQL Diff (esto es lo que uso pero hay muchas otras como herramientas de Red Gate o Dev Art) compararán dos bases de datos en un solo clic y generarán secuencias de comandos que se pueden ejecutar inmediatamente
• Contras: estos no son gratuitos (la mayoría de los proveedores tienen una versión de prueba totalmente funcional)

Vistas del sistema

• Pros: Puede ver fácilmente qué procedimientos almacenados existen en el servidor secundario y solo generar aquellos que no tiene.
• Contras: requiere un poco más de conocimiento de SQL

Aquí se muestra cómo obtener una lista de todos los procedimientos en alguna base de datos que no existen en otra base de datos

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

Answer 8

utilizar

select * from sys.procedures

para mostrar todos sus procedimientos;

sp_helptext @objname = ''Procedure_name''

para obtener el código

y tu creatividad para construir algo para recorrerlos a todos y generar el código de exportación :)

Answer 9

• Haga clic derecho en la base de datos
• Tareas
• Generar secuencias de comandos
• Seleccione los objetos que desea script
• Script para archivo
• Ejecutar scripts generados contra la base de datos de destino