SQL Server - copying stored procedures from one database to another

Question

I am new to SQL and I needed to combine 2.mdf databases into one. I did this using SQL Server 2008 Manager - Tasks> Import / Export tables. Tables and views were copied successfully, but there are no stored procedures in the new database. Is there any way to do this?

Answer 1

• Right click on database
• Tasks
• Generate scripts
• Select the objects you want to script
• Script to file
• Running the generated scripts with the target database

Answer 2

This code copies all stored procedures in the main database to the target database, you can copy only those procedures that you like by filtering the query by the name of the procedure.

@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 

Answer 3

Later, but gives more detailed information that may be useful ...

Here is a list of things you can do with the pros and cons

Generate scripts using SSMS

• Pros: extremely easy to use and supported by default
• Cons: scripts may not be in the correct order of execution, and you may get errors if the stored procedure already exists in the secondary database. Before you run, make sure you look at the script.

Third Party Tools

• Pros: such as ApexSQL Diff (this is what I use, but there are many others, such as tools from Red Gate or Dev Art) will compare two databases with one click and generate a script that you can execute immediately
• Cons: they are not free (most vendors have fully functional testing)

System views

• Pros:. You can easily see which stored procedures exist on the secondary server and generate only those that you do not have.
• Cons: Requires a little more knowledge of SQL

Here's 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) 

Answer 4

I initially found this message looking for a solution to copy stored procedures from my remote production database to my local development database. After success, using the proposed approach in this topic, I realized that I was becoming more and more lazy (or resourceful, depending on what you prefer) and wanted this to be automated. I came across this link , which turned out to be very useful (thanks vincpa), and I expanded 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 the task scheduler. After calling the Powershell file, I have:

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

This line will go through the directory and discard the procedures that it is going to recreate. If this were not a development environment, I would not want software to be discarded in this way. Then I will rename all 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 /bd:\prod_schema_backup\stp\') do sqlcmd /S localhost /d dest_db /E /i "%f".sql 

And it repeats through all .sql files and recreates stored procedures. I hope that any part of this will prove useful to someone.

Answer 5

You can use SSMS "Generate scripts ..." to script from what you need to pass. Right-click the source database in SSMS, select "Generate Scripts ..." and follow the instructions in the wizard. Then run the resulting script, which will now contain instructions for creating the stored procedure.

Answer 6

use

 select * from sys.procedures 

to show all your procedures;

 sp_helptext @objname = 'Procedure_name' 

to get the code

and your creativity to create something to iterate over all of them and generate an export code :)

Answer 7

You can generate a stored procedure script as shown in other answers. After creating the script, you can use sqlcmd to execute them against the target database, for example

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

Answer 8

In Mgmt Studio, right-click the source database, then Tasks, then generate scripts ... - follow the wizard.

Answer 9

Definition SELECT + char (13) + 'GO' FROM MyDatabase.sys.sql_modules s INNER JOIN MyDatabase.sys.procedures p ON [s]. [object_id] = [p]. [object_id] WHERE p. LIKE name 'Something%' "queryout" c: \ SP_scripts.sql -S MyInstance -T -t -w

get sp and execute it