I'm trying to generate scripts from a database, but with slight modifications to those objects. For example, I want to generate the script for a stored procedure, but I want the script to create it under a different schema.
I have an example of what I'm currently trying below. If I'm going in a completely wrong direction then please point me in the right direction. I want to avoid doing any kind of string search and replace since that has a lot of potential hazards.
What I have tried to do is create a function object from an existing function in a database, then I make a new one and try to make it a copy of the one that I just created. This seemed necessary to allow me to change the schema since it has to be in memory and not tied to a database. I then try to change the schema and script it out. Unfortunately, I haven't been able to find a syntax for .Script that doesn't fail with an error in PowerShell.
$instance = $args[0] # Gets the instance name from the command line parameters
$database = $args[1] # Gets the database name from the command line parameters
# Import the SMO assembly
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null
# Create an instance for the SQL Server
$serverInstance = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $instance
# Create an instance for the Database
$databaseInstance = $serverInstance.Databases[$database]
# Loop through all of the user defined functions (that are not system objects) in the database
foreach ($function in $databaseInstance.UserDefinedFunctions | Where-Object {$_.IsSystemObject -eq $False})
{
$newFunction = New-Object ('Microsoft.SqlServer.Management.Smo.UserDefinedFunction') ($databaseInstance, $function.Name, "tnf_dbo_func")
$newFunction.TextHeader = $function.TextHeader
$newFunction.TextBody = $function.TextBody
$newFunction.TextMode = $False
$newFunction.ChangeSchema("tnf_dbo_func")
$newFunction.TextMode = $True
Write-Host $newFunction.Schema
Write-Host $newFunction.TextHeader
Write-Host $newFunction.TextBody
$newFunction.Script()
}