functions/get-d365tablesequence.ps1

<#
.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
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
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
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
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:PSModuleRoot\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 {}
}