functions/get-d365tablefield.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
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201

<#
    .SYNOPSIS
        Get a field from table
         
    .DESCRIPTION
        Get a field either by FieldName (wildcard search allowed) or by FieldId
         
    .PARAMETER TableId
        The id of the table that the field belongs to
         
    .PARAMETER Name
        Name of the field that you are looking for
         
        Accepts wildcards for searching. E.g. -Name "Account*"
         
        Default value is "*" which will search for all fields
         
    .PARAMETER FieldId
        Id of the field that you are looking for
         
        Type is integer
         
    .PARAMETER DatabaseServer
        The name of the database server
         
        If on-premises or classic SQL Server, use either short name og Fully Qualified Domain Name (FQDN)
         
        If Azure use the full address to the database server, e.g. server.database.windows.net
         
    .PARAMETER DatabaseName
        The name of the database
         
    .PARAMETER SqlUser
        The login name for the SQL Server instance
         
    .PARAMETER SqlPwd
        The password for the SQL Server user
         
    .PARAMETER TableName
        Name of the table that the field belongs to
         
        Search will only return the first hit (unordered) and work against that hit
         
    .PARAMETER IncludeTableDetails
        Switch options to enable the result set to include extended details
         
    .PARAMETER SearchAcrossTables
        Switch options to force the cmdlet to search across all tables when looking for the field
         
    .EXAMPLE
        PS C:\> Get-D365TableField -TableId 10347
         
        Will get all field details for the table with id 10347.
         
    .EXAMPLE
        PS C:\> Get-D365TableField -TableName CustTable
         
        Will get all field details for the CustTable table.
         
    .EXAMPLE
        PS C:\> Get-D365TableField -TableId 10347 -FieldId 175
         
        Will get the details for the field with id 175 that belongs to the table with id 10347.
         
    .EXAMPLE
        PS C:\> Get-D365TableField -TableId 10347 -Name "VATNUM"
         
        Will get the details for the "VATNUM" that belongs to the table with id 10347.
         
    .EXAMPLE
        PS C:\> Get-D365TableField -TableId 10347 -Name "VAT*"
         
        Will get the details for all fields that fits the search "VAT*" that belongs to the table with id 10347.
         
    .EXAMPLE
        PS C:\> Get-D365TableField -Name AccountNum -SearchAcrossTables
         
        Will search for the AccountNum field across all tables.
         
    .EXAMPLE
        PS C:\> Get-D365TableField -TableName CustTable -IncludeTableDetails
         
        Will get all field details for the CustTable table.
        Will include table details in the output.
         
    .NOTES
        Tags: Table, Tables, Fields, TableField, Table Field, TableName, TableId
         
        Author: Mötz Jensen (@splaxi)
         
        The cmdlet supports piping and can be used in advanced scenarios. See more on github and the wiki pages.
         
         
#>

function Get-D365TableField {
    [CmdletBinding(DefaultParameterSetName = 'Default')]
    param (
        [Parameter(Mandatory = $true, ParameterSetName = 'Default', ValueFromPipelineByPropertyName = $true, Position = 1 )]
        [int] $TableId,

        [Parameter(Mandatory = $false, ParameterSetName = 'Default', Position = 2 )]
        [Parameter(Mandatory = $false, ParameterSetName = 'TableName', Position = 2 )]
        [Parameter(Mandatory = $false, ParameterSetName = 'SearchByNameForce', Position = 1 )]
        [string] $Name = "*",

        [Parameter(Mandatory = $false, ParameterSetName = 'Default', ValueFromPipelineByPropertyName = $true, Position = 3 )]
        [Parameter(Mandatory = $false, ParameterSetName = 'TableName', ValueFromPipelineByPropertyName = $true, Position = 3 )]
        [int] $FieldId,

        [Parameter(Mandatory = $false, ParameterSetName = 'Default', Position = 4 )]
        [Parameter(Mandatory = $false, ParameterSetName = 'TableName', Position = 4 )]
        [Parameter(Mandatory = $false, ParameterSetName = 'SearchByNameForce', Position = 3 )]
        [string] $DatabaseServer = $Script:DatabaseServer,

        [Parameter(Mandatory = $false, ParameterSetName = 'Default', Position = 5 )]
        [Parameter(Mandatory = $false, ParameterSetName = 'TableName', Position = 5 )]
        [Parameter(Mandatory = $false, ParameterSetName = 'SearchByNameForce', Position = 4 )]
        [string] $DatabaseName = $Script:DatabaseName,

        [Parameter(Mandatory = $false, ParameterSetName = 'Default', Position = 6 )]
        [Parameter(Mandatory = $false, ParameterSetName = 'TableName', Position = 6 )]
        [Parameter(Mandatory = $false, ParameterSetName = 'SearchByNameForce', Position = 5 )]
        [string] $SqlUser = $Script:DatabaseUserName,

        [Parameter(Mandatory = $false, ParameterSetName = 'Default', Position = 7 )]
        [Parameter(Mandatory = $false, ParameterSetName = 'TableName', Position = 7 )]
        [Parameter(Mandatory = $false, ParameterSetName = 'SearchByNameForce', Position = 6 )]
        [string] $SqlPwd = $Script:DatabaseUserPassword,

        [Parameter(Mandatory = $true, ParameterSetName = 'TableName', Position = 1 )]
        [string] $TableName,

        [Parameter(Mandatory = $false, ParameterSetName = 'Default')]
        [Parameter(Mandatory = $false, ParameterSetName = 'TableName')]
        [switch] $IncludeTableDetails,

        [Parameter(Mandatory = $true, ParameterSetName = 'SearchByNameForce', Position = 2 )]
        [switch] $SearchAcrossTables
    )
    BEGIN {
        $UseTrustedConnection = Test-TrustedConnection $PSBoundParameters

        $SqlParams = @{ DatabaseServer = $DatabaseServer; DatabaseName = $DatabaseName;
            SqlUser = $SqlUser; SqlPwd = $SqlPwd
        }

        $sqlCommand = Get-SqlCommand @SqlParams -TrustedConnection $UseTrustedConnection

    }
    
    PROCESS {
        if ($PSCmdlet.ParameterSetName -eq "TableName") {
            $TableId = (Get-D365Table -Name $TableName | Select-Object -First 1).TableId
        }

        if ($SearchAcrossTables) {
            $sqlCommand.CommandText = (Get-Content "$script:ModuleRoot\internal\sql\get-alltablefields.sql") -join [Environment]::NewLine
        }
        else {
            $sqlCommand.CommandText = (Get-Content "$script:ModuleRoot\internal\sql\get-tablefields.sql") -join [Environment]::NewLine
            $null = $sqlCommand.Parameters.Add("@TableId", $TableId)
        }

        $dataTable = New-Object system.Data.DataSet
        $dataAdapter = New-Object system.Data.SqlClient.SqlDataAdapter($sqlCommand)
        $dataAdapter.fill($dataTable) | Out-Null

        foreach ($obj in $dataTable.Tables.Rows) {
            if ($obj.FieldId -eq 0) {
                $TableName = $obj.AotName

                continue
            }

            if ($PSBoundParameters.ContainsKey("FieldId")) {
                if ($obj.FieldId -NotLike $FieldId) { continue }
            }
            else {
                if ($obj.AotName -NotLike $Name) { continue }
            }
            $res = [PSCustomObject]@{
                FieldId   = $obj.FieldId
                FieldName = $obj.AotName
                SqlName   = $obj.SqlName
            }

            if ($IncludeTableDetails) {
                $res | Add-Member -MemberType NoteProperty -Name 'TableId' -Value $obj.TableId
                $res | Add-Member -MemberType NoteProperty -Name 'TableName' -Value $TableName
            }
            if ($SearchAcrossTables) {
                $res | Add-Member -MemberType NoteProperty -Name 'TableId' -Value $obj.TableId
            }

            $res
        }
    }

    END {}
}