You need dbatools PowerShell module. This script will copy all missing 'View', 'TableValuedFunction', 'DefaultConstraint', 'StoredProcedure', 'Rule', 'InlineTableValuedFunction', 'ScalarFunction', 'Database Trigger', 'Table/View Triggers' from a source database to a destination one within same instance. No error handling if compilation fails (Ex…

CopyAllMissingModules.ps1

#Vars
$srcInstance = "localhost"
$destInstance = "localhost"
$srcDatabase = "db1"
$destDatabase = "db2"

# Will create SMO instance object that we will use to run script out the definition of the objects
$sqlSrcInst = Connect-DbaInstance $srcInstance

# Will create SMO instance object that we will use later to run the scripts on the destination database
$sqlDestInst = Connect-DbaInstance $destInstance

# Get existing modules on source database
$dbSrcModules = Get-DbaModule -SqlInstance $sqlSrcInst -Database $srcDatabase -ExcludeSystemObjects -Type 'View', 'TableValuedFunction', 'DefaultConstraint', 'StoredProcedure', 'Rule', 'InlineTableValuedFunction', 'ScalarFunction'

# Get existing modules on destination database
$dbDestModules = Get-DbaModule -SqlInstance $sqlDestInst -Database $destDatabase -ExcludeSystemObjects -Type 'View', 'TableValuedFunction', 'DefaultConstraint', 'StoredProcedure', 'Rule', 'InlineTableValuedFunction', 'ScalarFunction'


# Create smo object for the source database to get SMOs objects for each type of module
$smoSrcDb = $sqlSrcInst.Databases[$srcDatabase]
# Create smo object for the destination database to get SMOs objects for each type of module
$smoDestDb = $sqlSrcInst.Databases[$destDatabase]

# Get database triggers from source
$dbSrcModules += $smoSrcDb.Triggers | Where-Object IsSystemObject -ne $true | Select-Object Name, @{l = 'SchemaName'; e = { $null } } , @{l = 'Type'; e = { 'SQL_TRIGGER' } }, @{l = 'Database'; e = { $srcDatabase } }

# Get database triggers from destination
$dbDestModules += $smoDestDb.Triggers | Where-Object IsSystemObject -ne $true | Select-Object Name, @{l = 'SchemaName'; e = { $null } } , @{l = 'Type'; e = { 'SQL_TRIGGER' } }, @{l = 'Database'; e = { $destDatabase } }

# Get table triggers from source
$dbSrcModules += $smoSrcDb.Tables.Triggers | Where-Object IsSystemObject -ne $true | Select-Object Name, @{l = 'SchemaName'; e = { $null } } , @{l = 'Type'; e = { 'SQL_TABLE_TRIGGER' } }, @{l = 'Database'; e = { $srcDatabase } }

# Get table triggers from destination
$dbDestModules += $smoDestDb.Tables.Triggers | Where-Object IsSystemObject -ne $true | Select-Object Name, @{l = 'SchemaName'; e = { $null } } , @{l = 'Type'; e = { 'SQL_TABLE_TRIGGER' } }, @{l = 'Database'; e = { $destDatabase } }

# Filter only missing objects on destination
$missingObjects = $dbSrcModules | Where-Object {$dbDestModules.Name -notcontains $_.Name}

# List of missing objects
#$missingObjects | Select name, type    


$results = @()
foreach ($moduleObject in $missingObjects) {
    $smObject = switch ($moduleObject.Type) {
        "TABLE" { $smoSrcDb.Tables.Item($moduleObject.Name, $moduleObject.SchemaName) }
        "VIEW" { $smoSrcDb.Views.Item($moduleObject.Name, $moduleObject.SchemaName) }
        "SQL_STORED_PROCEDURE" { $smoSrcDb.StoredProcedures.Item($moduleObject.Name, $moduleObject.SchemaName) }
        "RULE" { $smoSrcDb.Rules.Item($moduleObject.Name, $moduleObject.SchemaName) }
        "SQL_TRIGGER" { $smoSrcDb.Triggers.Item($moduleObject.Name) }
        "SQL_TABLE_TRIGGER" { Get-DbaDbObjectTrigger -SqlInstance $srcInstance -Database $srcDatabase | Where-Object Name -eq $moduleObject.Name }
        "SQL_TABLE_VALUED_FUNCTION" { $smoSrcDb.UserDefinedFunctions.Item($moduleObject.Name, $moduleObject.SchemaName) }
        "SQL_INLINE_TABLE_VALUED_FUNCTION" { $smoSrcDb.UserDefinedFunctions.Item($moduleObject.Name, $moduleObject.SchemaName) }
        "SQL_SCALAR_FUNCTION" { $smoSrcDb.UserDefinedFunctions.Item($moduleObject.Name, $moduleObject.SchemaName) }
    }
    $results += $smObject
}

# Generate the scripting options
$ScriptingOptionsObject = New-DbaScriptingOption
$ScriptingOptionsObject.IncludeDatabaseContext = $false
$ScriptingOptionsObject.ScriptBatchTerminator = $true
$ScriptingOptionsObject.AnsiFile = $true

$export = @{
                NoPrefix         = $NoPrefix
                ScriptingOptions = $ScriptingOptionsObject
            }


# Generated T-SQL Script all objects
foreach($r in $results) {
    try {
        Write-Host "Exporting $($r.Type) : $($r.Name)"
        $exportedScript = $r | Export-DbaScript @export -PassThru

        # Invoke-DbaQuery is not suitable for this (due GO's - will run each batch)
        # Workaround - Run the changed script using the ExecuteNonQuery method (we don't need to remove the 'GO's
        $sqlDestInst.Databases["$destDatabase"].ExecuteNonQuery($exportedScript)

        Write-Host "Executing $($r.Type) : $($r.Name)"
    }
    catch {
        Write-Host "Failed executing $($r.Type) : $($r.Name)" -ForegroundColor Red
    }
}