internal/functions/Get-DbaSysDbUserObjectScript.ps1

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
function Get-DbaSysDbUserObjectScript {
    <#
        .SYNOPSIS
            Gets all user objects found in source SQL Server's master, msdb and model databases to the destination.
    #>

    [CmdletBinding()]
    param (
        [parameter(Mandatory, ValueFromPipeline)]
        [Alias("ServerInstance", "SqlServer")]
        [DbaInstanceParameter]$SqlInstance,
        [PSCredential]$SqlCredential,
        [switch]$EnableException
    )
    begin {
        function get-sqltypename ($type) {
            switch ($type) {
                "VIEW" { "view" }
                "SQL_TABLE_VALUED_FUNCTION" { "User table valued fsunction" }
                "DEFAULT_CONSTRAINT" { "User default constraint" }
                "SQL_STORED_PROCEDURE" { "User stored procedure" }
                "RULE" { "User rule" }
                "SQL_INLINE_TABLE_VALUED_FUNCTION" { "User inline table valued function" }
                "SQL_TRIGGER" { "User server trigger" }
                "SQL_SCALAR_FUNCTION" { "User scalar function" }
                default { $type }
            }
        }
    }
    process {
        try {
            $server = Connect-SqlInstance -SqlInstance $SqlInstance -SqlCredential $SqlCredential
        } catch {
            Stop-Function -Message "Failure" -Category ConnectionError -ErrorRecord $_ -Target $Source
            return
        }
        
        if (!(Test-SqlSa -SqlInstance $server -SqlCredential $SqlCredential)) {
            Stop-Function -Message "Not a sysadmin on $source. Quitting."
            return
        }
        
        $systemDbs = "master", "model", "msdb"
        
        foreach ($systemDb in $systemDbs) {
            $smodb = $server.databases[$systemDb]
            $destdb = $server.databases[$systemDb]
            Write-Output "USE $systemDb"
            Write-Output "GO"
            $tables = $smodb.Tables | Where-Object IsSystemObject -ne $true
            $schemas = $smodb.Schemas | Where-Object IsSystemObject -ne $true
            $transfer = New-Object Microsoft.SqlServer.Management.Smo.Transfer $smodb
            $null = $transfer.CopyAllObjects = $false
            $null = $transfer.Options.WithDependencies = $true
            $null = $transfer.ObjectList.Add($schema)
            $null = $transfer.Options.ScriptBatchTerminator = $true
            try { $transfer.ScriptTransfer() }
            catch { }
            Write-Output "GO"
            
            foreach ($table in $tables) {
                Write-Output "GO"
                $transfer = New-Object Microsoft.SqlServer.Management.Smo.Transfer $smodb
                $null = $transfer.CopyAllObjects = $false
                $null = $transfer.Options.WithDependencies = $true
                $null = $transfer.Options.ScriptBatchTerminator = $true
                $null = $transfer.ObjectList.Add($table)
                try { $transfer.ScriptTransfer() } catch {}
            }
            
            $userobjects = Get-DbaModule -SqlInstance $server -Database $systemDb -NoSystemObjects | Sort-Object Type
            Write-Message -Level Verbose -Message "Copying from $systemDb"
            foreach ($userobject in $userobjects) {
                Write-Output "GO"
                $name = "[$($userobject.SchemaName)].[$($userobject.Name)]"
                $db = $userobject.Database
                $type = get-sqltypename $userobject.Type
                $userobject.Definition
                $schema = $userobject.SchemaName
                $result = Get-DbaModule -SqlInstance $server -NoSystemObjects -Database $db |
                    Where-Object { $psitem.Name -eq $userobject.Name -and $psitem.Type -eq $userobject.Type }
                $smobject = switch ($userobject.Type) {
                    "VIEW" { $smodb.Views.Item($userobject.Name, $userobject.SchemaName) }
                    "SQL_STORED_PROCEDURE" { $smodb.StoredProcedures.Item($userobject.Name, $userobject.SchemaName) }
                    "RULE" { $smodb.Rules.Item($userobject.Name, $userobject.SchemaName) }
                    "SQL_TRIGGER" { $smodb.Triggers.Item($userobject.Name, $userobject.SchemaName) }
                    "SQL_TABLE_VALUED_FUNCTION" { $smodb.UserDefinedFunctions.Item($name) }
                    "SQL_INLINE_TABLE_VALUED_FUNCTION" { $smodb.UserDefinedFunctions.Item($name) }
                    "SQL_SCALAR_FUNCTION" { $smodb.UserDefinedFunctions.Item($name) }
                }
                
                $smobject = switch ($userobject.Type) {
                    "VIEW" { $smodb.Views.Item($userobject.Name, $userobject.SchemaName) }
                    "SQL_STORED_PROCEDURE" { $smodb.StoredProcedures.Item($userobject.Name, $userobject.SchemaName) }
                    "RULE" { $smodb.Rules.Item($userobject.Name, $userobject.SchemaName) }
                    "SQL_TRIGGER" { $smodb.Triggers.Item($userobject.Name, $userobject.SchemaName) }
                }
                if ($smobject) {
                    $transfer = New-Object Microsoft.SqlServer.Management.Smo.Transfer $smodb
                    $null = $transfer.CopyAllObjects = $false
                    $null = $transfer.Options.WithDependencies = $true
                    $null = $transfer.ObjectList.Add($smobject)
                    $null = $transfer.Options.ScriptBatchTerminator = $true
                    try { $transfer.ScriptTransfer() } catch {}
                }
            }
        }
    }
}