SQL Server – copie des procédures stockées d’une firebase database vers une autre

Je suis novice en SQL et je devais combiner 2 bases de données .mdf en une seule. Je l’ai fait en utilisant SQL Server 2008 Manager – Tâches> Importer / Exporter des tables. Les tables et les vues ont été copiées avec succès, mais il n’y a pas de procédures stockées dans la nouvelle firebase database. Y’a-t’il un quelconque moyen d’y arriver?

  • Clic droit sur la firebase database
  • les tâches
  • Générer des scripts
  • Sélectionnez les objects que vous souhaitez écrire
  • Script à classer
  • Exécuter des scripts générés sur la firebase database cible

Ce code copie toutes les procédures stockées dans la firebase database principale sur la firebase database cible. Vous pouvez copier uniquement les procédures qui vous conviennent en filtrant la requête sur le nom de la procédure.

@sql est défini comme nvarchar (max), @Name est la firebase database cible

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 

Plus tard mais donne plus de détails qui pourraient être utiles…

Voici une liste de choses que vous pouvez faire avec les avantages et les inconvénients

Générer des scripts en utilisant SSMS

  • Avantages: extrêmement facile à utiliser et supporté par défaut
  • Inconvénients: les scripts peuvent ne pas être dans le bon ordre d’exécution et vous pouvez obtenir des erreurs si la procédure stockée existe déjà sur la firebase database secondaire. Assurez-vous de revoir le script avant de l’exécuter.

Outils tiers

  • Avantages: des outils tels que ApexSQL Diff (c’est ce que j’utilise mais il y en a beaucoup d’autres comme les outils de Red Gate ou de Dev Art) compareront deux bases de données en un clic et génèreront un script que vous pourrez exécuter immédiatement
  • Inconvénients: ceux-ci ne sont pas gratuits (la plupart des fournisseurs ont un essai entièrement fonctionnel cependant)

Vues système

  • Avantages: Vous pouvez facilement voir quelles procédures stockées existent sur le serveur secondaire et générer uniquement celles que vous n’avez pas.
  • Contre: Nécessite un peu plus de connaissances SQL

Voici comment obtenir une liste de toutes les procédures de certaines bases de données qui n’existent pas dans une autre firebase database.

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

J’ai initialement trouvé ce post à la recherche d’une solution pour copier les procédures stockées de ma firebase database de production distante vers ma firebase database de développement local. Après avoir utilisé avec succès l’approche suggérée dans ce fil de discussion, je me suis rendu compte que je devenais de plus en plus paresseux (ou plein de ressources, quel que soit votre choix) et que je souhaitais que cela soit automatisé. Je suis tombé sur ce lien , qui s’est avéré très utile (merci à vincpa), et je l’ai prolongé, ce qui a abouti au fichier suivant (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() 

J’ai un fichier .bat qui appelle cela et est appelé à partir du Planificateur de tâches. Après l’appel au fichier Powershell, j’ai:

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

Cette ligne passera par le répertoire et abandonnera les procédures qu’elle va recréer. Si ce n’était pas un environnement de développement, je n’aimerais pas que les procédures soient supprimées par programmation. Je renomme alors tous les fichiers de procédure stockés pour avoir .sql:

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

Et puis lancez:

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

Et cela parcourt tous les fichiers .sql et recrée les procédures stockées. J’espère que toute partie de cela se révélera utile à quelqu’un.

Vous pouvez utiliser la fonction “Generate Scripts …” de SSMS pour créer le script que vous souhaitez transférer. Cliquez avec le bouton droit sur la firebase database source dans SSMS, choisissez “Générer des scripts …”, puis suivez les instructions de l’assistant. Ensuite, exécutez votre script résultant qui contiendra désormais les instructions de création de la procédure stockée.

utilisation

 select * from sys.procedures 

montrer toutes vos procédures;

 sp_helptext @objname = 'Procedure_name' 

pour obtenir le code

et votre créativité pour construire quelque chose pour les parcourir tous et générer le code d’exportation 🙂

Vous pouvez générer un script de la procédure stockée comme indiqué dans d’autres réponses. Une fois le script généré, vous pouvez utiliser sqlcmd pour les exécuter sur la firebase database cible, par exemple

 sqlcmd -S  -U  -d  -i  

Dans Mgmt Studio, cliquez avec le bouton droit sur votre firebase database d’origine, puis sur Tâches, puis sur Générer des scripts … – suivez l’assistant.

SELECT selection + 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 ‘ ‘”queryout” c: \ SP_scripts.sql -S MyInstance -T -t -w

obtenir le sp et l’exécuter