Run, parse and present the output of SQL Server's "exec sp_help" on a table

sp_help.ps1

param([string] $table_name)

# -------------------
# Auxiliary functions
# -------------------

function f([string] $code, [string] $text) {
    if ($Host.UI.SupportsVirtualTerminal) {
        [char] $ESC = 27
        "$ESC[${code}m" + $text + "$ESC[0m"
    } else {
        $text
    }
}

function l([string] $query = '') {
    $DB = @{
        Encrypt = 'Optional'
        ServerInstance = '...'
        Database = '...'
    }
    Invoke-Sqlcmd @DB -Query $query @args
}

##################
# Fetch the data #
##################
Write-Host -NoNewline -ForegroundColor DarkGray "Fetching data...`r"

$query = @"
select count(*) as count from $table_name  --> $count
select top(10) *         from $table_name  --> $sample
exec sp_help $table_name                   --> $details[2..]
"@

$details = (l $query -OutputAs DataSet).Tables
# $details | ForEach-Object -Begin { $i = 0 } `
#                           -Process { "===`n $i`n===`n"; $_ | Format-Table; $i++ }

##################
# Parse the data #
##################
$count = $details[0]
$sample = $details[1]
$the_table = $details[2]
$columns = $details[3]
$indexes = $details[7]
$constraints = $details[8]
$referenced_by_t = $details[9]
$referenced_by_v = $details[10]

$primary_key = $constraints | where constraint_type -like 'PRIMARY KEY*' | % { $_.constraint_keys -split ', ' }
$unique_keys = $constraints | where constraint_type -like 'UNIQUE*'      | % { $_.constraint_keys -split ', ' }
$other_tables = $referenced_by_t.'Table is referenced by foreign key' | % {
    [regex]::Match($_, '(?:[^.]*\.)*([^:]*)').Groups[1].Value
}
$other_views = $referenced_by_v.'Table is referenced by views'
$referenced_tables = $(
    foreach ($constraint in $( try { $constraints.GetList() } catch { ,@() })) {
        if ( $constraint.constraint_type -ne 'FOREIGN KEY' ) { continue }
        [pscustomobject] @{ col = $constraint.constraint_keys
                          ; FK = $( [void] $foreach.MoveNext()
                                     $foreach.Current.constraint_keys -match '(?:[^.]*\.)*(\S*)\s+(\([^)]*\))' | Out-Null
                                     "---$($matches[2])--> $($matches[1])" )
                          ; ON = "$(if ($matches) { $matches[2] } else { "<nope>" })"
                          ; referenced_table = "$(if ($matches) { $matches[1] } else { "nope" })" }
    }
)

########################################################
# Cute table summary in Unicode box-drawing characters #
########################################################
Write-Host -ForegroundColor DarkGray (([char]0x256d) + "$([char]0x2500)" * 60 + ([char]0x256e))
Write-Host -NoNewline "$(f 2 ([char]0x2502))       Table: "
Write-Host -NoNewline -ForegroundColor Green $the_table.Name
Write-Host -ForegroundColor DarkGray (" " * (60 - 14 - $the_table.Name.Length) + ([char]0x2502))
Write-Host -NoNewline "$(f 2 ([char]0x2502)) PRIMARY KEY: "
$t = "$($primary_key -join ', ') $([char]::ConvertFromUtf32(0x1F511))"  # Key emoji
Write-Host -NoNewline -ForegroundColor Blue $t
Write-Host -ForegroundColor DarkGray (" " * (60 - 14 - $t.Length) + ([char]0x2502))
if ($unique_keys) {
    Write-Host -NoNewline "$(f 2 ([char]0x2502))      UNIQUE: "
    $t = $unique_keys -join ', '
    Write-Host -NoNewline -ForegroundColor DarkCyan $t
    if ($t.Length -lt (60 - 14)) {
        Write-Host -ForegroundColor DarkGray (" " * (60 - 14 - $t.Length) + ([char]0x2502))
    } else {
        Write-Host
    }
}
Write-Host -ForegroundColor DarkGray (([char]0x2570) + "$([char]0x2500)" * 60 + ([char]0x256f))

#################
# Columns table #
#################
$Column_prop = @{
    name = "Column name"
    Expression = {
        $col = $(switch ($_.Column_Name) {
            { $_ -in $primary_key } { f 34 $_ }
            { $_ -in $unique_keys } { f 36 $_ }
            default { $_ }
        })
        $rt = $referenced_tables | where col -eq $_.Column_Name
        if ($rt) {
            $col = $col `
               -replace "$($rt.referenced_table)", [regex]::Unescape('\e[32m$0\e[37m') `
               -replace "$($rt.ON)",               [regex]::Unescape('\e[34m$0\e[37m')
        }
        if ($_.Column_Name -in $primary_key -or $_.Column_name -in $unique_keys) {
            $col = f 4 $col
        }
        $col
    }
}
$FK_prop = @{
    Name = "REFERENCES"
    Expression = {
        ($referenced_tables | where col -eq $_.Column_Name).FK `
            -replace '(?<=\()[^)]+', (f 34 '$0') `
            -replace '(?<=--> ).+',  (f 32 '$0')
    }
}
$columns | Format-Table $Column_prop, Type, Length, Prec, Nullable, $FK_prop

################
# Table sample #
################
$saved_foreground = $Host.UI.RawUI.ForegroundColor
$saved_background = $Host.UI.RawUI.BackgroundColor
$Host.UI.RawUI.ForegroundColor = [ConsoleColor]::DarkCyan

if ($count.count -gt 0) {
    Write-Host -ForegroundColor DarkGray ("$([char]0x2581)" * $host.UI.RawUI.WindowSize.Width)
    # $Host.UI.RawUI.BackgroundColor = [ConsoleColor]::DarkGray
    $sample | Format-Table
    $offset_y = { param($dy);
                  $host.UI.RawUI.CursorPosition = $host.UI.RawUI.CursorPosition | % { $_.Y += $dy; $_ } }
    # & $offset_y -11
    # Write-Host -ForegroundColor DarkGray ("$([char]0x2501)" * $host.UI.RawUI.WindowSize.Width)
    # & $offset_y 9
    & $offset_y -1
    if ($count.count -gt 10) {
        Write-Host -ForegroundColor DarkGray (f 3 "+ $($count.count - 10) rows...")
    }
    # $Host.UI.RawUI.BackgroundColor = $saved_background
    Write-Host -ForegroundColor DarkGray ("$([char]0x2594)" * $host.UI.RawUI.WindowSize.Width)
    Write-Host
} else {
    Write-Host -ForegroundColor DarkGray (f 3 "  * This table is empty`n")
}

$Host.UI.RawUI.ForegroundColor = [ConsoleColor]::DarkGray

################################
# Referencing tables and views #
################################
if ($other_tables -or $other_views) {
    Write-Host -ForegroundColor Cyan "Referenced by"
    if ($other_tables) {
        Write-Host -NoNewline -ForegroundColor Green "  -> Tables:"
        $other_tables | Format-Wide -Force -Property { $_ } -AutoSize
    }
    if ($other_views) {
        Write-Host -NoNewline -ForegroundColor Yellow " -> Views:"
        $referenced_by_v | Format-Wide -AutoSize
    }
} else {
    Write-Host (f 3 "  * This table isn't referenced by any tables or views`n")
}

# if ($constraints) {
#   Write-Host -ForegroundColor Cyan "Foreign keys:`n============="
#   $referenced_tables | Format-Table -HideTableHeaders
# }

$Host.UI.RawUI.ForegroundColor = $saved_foreground


# To debug $details:
# ----------------------
# $details | ForEach-Object -Begin { $i = 0 } `
#                           -Process { "===`n $i`n===`n"; $_ | Format-Table; $i++ } "