functions/get-d365table.ps1

<#
.SYNOPSIS
Get a table
 
.DESCRIPTION
Get a table either by TableName (wildcard search allowed) or by TableId
 
.PARAMETER Name
Name of the table that you are looking for
 
Accepts wildcards for searching. E.g. -Name "Cust*"
 
Default value is "*" which will search for all tables
 
.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 Id
The specific id for the table you are looking for
 
.EXAMPLE
Get-D365Table -Name CustTable
 
Will get the details for the CustTable
 
.EXAMPLE
Get-D365Table -Id 10347
 
Will get the details for the table with the id 10347
 
.NOTES
The cmdlet supports piping and can be used in advanced scenarios. See more on github and the wiki pages.
 
#>

function Get-D365Table {
    [CmdletBinding(DefaultParameterSetName = 'Default')]
    param (
        [Parameter(Mandatory = $false, ParameterSetName = 'Default', Position = 1 )]
        [string[]] $Name = "*",

        [Parameter(Mandatory = $false, ParameterSetName = 'Default', Position = 2 )]
        [Parameter(Mandatory = $false, ParameterSetName = 'TableId', Position = 2 )]
        [string] $DatabaseServer = $Script:DatabaseServer,

        [Parameter(Mandatory = $false, ParameterSetName = 'Default', Position = 3 )]
        [Parameter(Mandatory = $false, ParameterSetName = 'TableId', Position = 3 )]
        [string] $DatabaseName = $Script:DatabaseName,

        [Parameter(Mandatory = $false, ParameterSetName = 'Default', Position = 4 )]
        [Parameter(Mandatory = $false, ParameterSetName = 'TableId', Position = 4 )]
        [string] $SqlUser = $Script:DatabaseUserName,

        [Parameter(Mandatory = $false, ParameterSetName = 'Default', Position = 5 )]
        [Parameter(Mandatory = $false, ParameterSetName = 'TableId', Position = 5 )]
        [string] $SqlPwd = $Script:DatabaseUserPassword,

        [Parameter(Mandatory = $true, ParameterSetName = 'TableId', Position = 1 )]
        [int] $Id
    )

    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
        }

        $sqlCommand = Get-SQLCommand $DatabaseServer $DatabaseName $SqlUser $SqlPwd

        $sqlCommand.CommandText = (Get-Content "$script:PSModuleRoot\internal\sql\get-tables.sql") -join [Environment]::NewLine

        $datatable = New-Object system.Data.DataSet
        $dataadapter = New-Object system.Data.SqlClient.SqlDataAdapter($sqlcommand)
        $dataadapter.fill($datatable) | Out-Null

        foreach ($localName in $Name) {
            if ($PSCmdlet.ParameterSetName -eq "Default") {
                foreach ($obj in $datatable.Tables.Rows) {
                    if ($obj.AotName -NotLike $localName) { continue }
                    [PSCustomObject]@{
                        TableId   = $obj.TableId
                        TableName = $obj.AotName
                        SqlName   = $obj.SqlName
                    }
                }
            }
            else {
                $obj = $datatable.Tables.Rows | Where-Object TableId -eq $Id | Select-Object -First 1
                [PSCustomObject]@{
                    TableId   = $obj.TableId
                    TableName = $obj.AotName
                    SqlName   = $obj.SqlName
                }
            }
        }
    }

    END {}
}