functions/get-d365tablefield.ps1
|
<#
.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 Get-D365TableField -TableId 10347 Will get all field details for the table with id 10347 .EXAMPLE Get-D365TableField -TableName CustTable Will get all field details for the CustTable table .EXAMPLE 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 Get-D365TableField -TableId 10347 -Name "VATNUM" Will get the details for the "VATNUM" that belongs to the table with id 10347 .EXAMPLE 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 Get-D365TableField -Name AccountNum -SearchAcrossTables Will search for the AccountNum field across all tables. .NOTES The cmdlet supports piping and can be used in advanced scenarios. See more on github and the wiki pages. Author: Mötz Jensen (@splaxi) #> 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 = $true, 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 {} PROCESS { if (!$script:IsAdminRuntime -and !($PSBoundParameters.ContainsKey("SqlPwd"))) { Write-Host "It seems that you ran this cmdlet non-elevated and without the -SqlPwd parameter. If you don't want to supply the -SqlPwd you must run the cmdlet elevated (Run As Administrator) or simply use the -SqlPwd parameter" -ForegroundColor Yellow Write-Error "Running non-elevated and without the -SqlPwd parameter. Please run elevated or supply the -SqlPwd parameter." -ErrorAction Stop } if ($PSCmdlet.ParameterSetName -eq "TableName") { $TableId = (Get-D365Table -Name $TableName | Select-Object -First 1).TableId } $sqlCommand = Get-SqlCommand $DatabaseServer $DatabaseName $SqlUser $SqlPwd if ($SearchAcrossTables.IsPresent) { $sqlCommand.CommandText = (Get-Content "$script:PSModuleRoot\internal\sql\get-alltablefields.sql") -join [Environment]::NewLine } else { $sqlCommand.CommandText = (Get-Content "$script:PSModuleRoot\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.IsPresent) { $res | Add-Member -MemberType NoteProperty -Name 'TableId' -Value $obj.TableId $res | Add-Member -MemberType NoteProperty -Name 'TableName' -Value $TableName } if ($SearchAcrossTables.IsPresent) { $res | Add-Member -MemberType NoteProperty -Name 'TableId' -Value $obj.TableId } $res } } END {} } |