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

<#
    .SYNOPSIS
        Get the sequence object for table
         
    .DESCRIPTION
        Get the sequence details for tables
         
    .PARAMETER TableName
        Name of the table that you want to work against
         
        Accepts wildcards for searching. E.g. -TableName "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.
         
    .EXAMPLE
        PS C:\> Get-D365TableSequence | Format-Table
         
        This will get all the sequence details for all tables inside the database.
        It will format the output as a table for better overview.
         
    .EXAMPLE
        PS C:\> Get-D365TableSequence -TableName "Custtable" | Format-Table
         
        This will get the sequence details for the CustTable in the database.
        It will format the output as a table for better overview.
         
    .EXAMPLE
        PS C:\> Get-D365TableSequence -TableName "Cust*" | Format-Table
         
        This will get the sequence details for all tables that matches the search "Cust*" in the database.
        It will format the output as a table for better overview.
         
    .EXAMPLE
        PS C:\> Get-D365Table -Name CustTable | Get-D365TableSequence | Format-Table
         
        This will get the table details from the Get-D365Table cmdlet and pipe that into Get-D365TableSequence.
        This will get the sequence details for the CustTable in the database.
        It will format the output as a table for better overview.
         
    .NOTES
        Author: Mötz Jensen (@Splaxi)
         
#>

function Get-D365TableSequence {
    [CmdletBinding()]
    param (
        [Parameter(Mandatory = $false, ValueFromPipelineByPropertyName = $true, Position = 1 )]
        [Alias('Name')]
        [string] $TableName = "*",

        [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-tablesequence.sql") -join [Environment]::NewLine
        $null = $sqlCommand.Parameters.AddWithValue('@TableName', $TableName.Replace("*", "%"))
        
        $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) {
            $res = [PSCustomObject]@{
                SequenceName   = $obj.sequence_name
                TableName = $obj.table_name
                StartValue   = $obj.start_value
                Increment = $obj.increment
                MinimumValue = $obj.minimum_value
                MaximumValue = $obj.maximum_value
                IsCached = $obj.is_cached
                CacheSize = $obj.cache_size
                CurrentValue = $obj.current_value
            }

            $res
        }
    }

    END {}
}