functions/get-d365table.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

<#
    .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
        PS C:\> Get-D365Table -Name CustTable
         
        Will get the details for the CustTable
         
    .EXAMPLE
        PS C:\> Get-D365Table -Id 10347
         
        Will get the details for the table with the id 10347.
         
    .NOTES
        Tags: Table, Tables, AOT, TableId, Development
         
        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-D365Table {
    [CmdletBinding(DefaultParameterSetName = 'Default')]
    param (
        [Parameter(Mandatory = $false, ParameterSetName = 'Default', Position = 1 )]
        [string[]] $Name = "*",

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

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

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

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

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

    BEGIN {}

    PROCESS {

        $UseTrustedConnection = Test-TrustedConnection $PSBoundParameters

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

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

        $sqlCommand.CommandText = (Get-Content "$script:ModuleRoot\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 {}
}