Get-Statement.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
function Get-Statement ($Statement, $Keys, $ScriptName) {
    $StatementObject = [PSCustomObject] @{
        PSTypeName      = "Parser.DOM.Statement"
        ScriptName      = $ScriptName
        BatchNumber     = $TotalBatches
        StatementNumber = $TotalStatements
        StatementType   = $null
        Action          = $null
        IsQualified     = $false
        OnObjectSchema  = $null
        OnObjectName    = $null
        Text            = $null
    }

    Add-Member -InputObject $StatementObject -Type ScriptMethod -Name ToString -Value { $this.psobject.typenames[0] } -Force

    $StatementObject.Action = $Statement.ScriptTokenStream[$Statement.FirstTokenIndex].Text.ToUpper()
    $StatementObject.Text = $Statement.ScriptTokenStream[$Statement.FirstTokenIndex..$Statement.LastTokenIndex].Text -join ""

    if ($statementObject.Action -eq "If") {
        Write-Verbose "Found an an 'IF' statement, looking at the 'THEN' part of the statement..."
        if ($Statement.ThenStatement.psobject.Properties["StatementList"] -and $Statement.ThenStatement.StatementList.Statements.Count -ge 1) {
            $SubStatements = $Statement.ThenStatement.StatementList.Statements
            ForEach ($su in $subStatements) {
                $StatementObject = Get-Statement $su $keys $ScriptName
                $StatementObject
            }
        } else {
            $StatementObject = Get-Statement $Statement.ThenStatement $keys $ScriptName
            $StatementObject
        }
    } else {
        $Property = $Statement
        $TempObjectType = ($Keys | Where-Object {$_.ObjectType -eq $Statement.gettype().name})
        if ($TempObjectType) {
            $ObjectType = $TempObjectType.ObjectType
            Write-Verbose "Object type: $ObjectType"
            if ($ObjectType -eq "UpdateStatement" -and $null -ne $statement.UpdateSpecification.WhereClause -and $null -ne $statement.UpdateSpecification.SetClauses) {
                if ($Statement.UpdateSpecification.FromClause) {
                    if ($Statement.UpdateSpecification.FromClause.TableReferences.psobject.Properties["FirstTableReference"]) {
                        $SchemaObject = Get-UpdatedTableFromReference $Statement.UpdateSpecification.FromClause.TableReferences.FirstTableReference
                    } else {
                        $SchemaObject = $Statement.UpdateSpecification.FromClause.TableReferences.SchemaObject
                    }
                } else {
                    $SchemaObject = $Statement.UpdateSpecification.Target.SchemaObject
                }
                if ($null -ne $SchemaObject.SchemaIdentifier) {
                    $StatementObject.OnObjectSchema = $SchemaObject.SchemaIdentifier.Value
                }
                if ($null -ne $SchemaObject.BaseIdentifier) {
                    $StatementObject.OnObjectName = $SchemaObject.BaseIdentifier.Value
                }
        } elseif ($ObjectType -eq "SelectStatement" -and $statement.QueryExpression.FromClause -and $statement.QueryExpression.FromClause.psobject.Properties["TableReferences"] -and $statement.QueryExpression.FromClause.TableReferences -and $statement.QueryExpression.FromClause.TableReferences.psobject.Properties["FirstTableReference"] -and $null -ne $statement.QueryExpression.FromClause.TableReferences.FirstTableReference) {
                $SchemaObject = Get-UpdatedTableFromReference $statement.Queryexpression.FromClause.TableReferences.FirstTableReference
                $StatementObject.OnObjectSchema = $SchemaObject.SchemaIdentifier.Value
                $StatementObject.OnObjectName = $SchemaObject.BaseIdentifier.Value
            } elseif ($ObjectType -eq "SelectStatement") {
                $StatementObject.StatementType = $Statement.GetType().Name.ToString()
            } elseif ($ObjectType -eq "PredicateSetStatement") {
                $StatementObject.StatementType = $Statement.GetType().Name.ToString()
                $StatementObject.OnObjectSchema = $Property.Options
                $StatementObject.OnObjectName = switch ($Property.IsOn) { $true { "ON" } $false { "OFF" } }
            } elseif ($ObjectType -eq "GrantStatement") {
                $StatementObject.StatementType = $Statement.GetType().Name.ToString()
                if ($Property.SecurityTargetObject) {
                    if ($Property.SecurityTargetObject.ObjectName.MultiPartIdentifier.Identifiers.Count -eq 1) {
                        $StatementObject.OnObjectName = $Property.SecurityTargetObject.ObjectName.MultiPartIdentifier.Identifiers[0].Value
                    } else {
                        $StatementObject.OnObjectSchema = $Property.SecurityTargetObject.ObjectName.MultiPartIdentifier.Identifiers[0].Value
                        $StatementObject.OnObjectName = $Property.SecurityTargetObject.ObjectName.MultiPartIdentifier.Identifiers[1].Value
                    }
                }
            } elseif ($ObjectType -eq "ExecuteStatement" -and $Statement.ExecuteSpecification.ExecutableEntity -is [Microsoft.SqlServer.TransactSql.ScriptDom.ExecutableStringList]) {
                $StatementObject.StatementType = $Statement.GetType().Name.ToString()
            } elseif ($ObjectType -in "DeclareVariableStatement", "PrintStatement", "CreateLoginStatement") {
                $StatementObject.StatementType = $Statement.GetType().Name.ToString()
            } else {
                try {
                    $StatementObject.StatementType = $Statement.GetType().Name.ToString()
                    $SplitDefinition = (($Keys | Where-Object {$_.ObjectType -eq $Statement.gettype().name}).SchemaSpecification).Split(".")
                    ForEach ($def in $SplitDefinition) {
                        $Property = $Property | Select-Object -ExpandProperty $def
                    }
                    if ($null -ne $Property.SchemaIdentifier) {
                        $StatementObject.OnObjectSchema = $Property.SchemaIdentifier.Value
                    }
                    if ($null -ne $Property.BaseIdentifier) {
                        $StatementObject.OnObjectName = $Property.BaseIdentifier.Value
                    }
                } catch {
                    Write-Warning "Parsed statement $($Statement.gettype().name) has no descernible statement type. Maybe define one as a parser key?"
                }
            }

            if ($StatementObject) {
                return $StatementObject
            }
        } else {
            Write-Warning "$($Statement.gettype().name) is not a known type"
        }
    }
}