I am new to SQL, and what I needed to do was to combine 2 .mdf databases into one. I did that using SQL Server 2008 Manager - Tasks > Import/Export tables.The tables and views were copied successfully, but there are no Stored procedures in the new database. Is there any way to do that?
This question is related to
sql
sql-server-2008
copy
procedures
Late one but gives more details that might be useful…
Here is a list of things you can do with advantages and disadvantages
Generate scripts using SSMS
Third party tools
System Views
Here is 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)
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
I originally found this post looking for a solution to copying stored procedures from my remote production database to my local development database. After success using the suggested approach in this thread, I realized I grew increasingly lazy (or resourceful, whichever you prefer) and wanted this to be automated. I came across this link, which proved to be very helpful (thank you vincpa), and I extended upon 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 Task Scheduler. After the call to the Powershell file, I have:
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.
Another option is to transfer stored procedures using SQL Server Integration Services (SSIS). There is a task called Transfer SQL Server Objects Task. You can use the task to transfer the following items:
It's a graphical tutorial for Transfer SQL Server Objects Task.
This code copies all stored procedures in the Master database to the target database, you can copy just the procedures you like by filtering the query on procedure name.
@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
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>
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 :)
Source: Stackoverflow.com