Propose an SQL Table Design

My team mate, Erik van Noorden, created this PowerShell function to easily create an SQL table design by passing in an array of PowerShell objects.

Think about when you retrieve data from an API. You might always get the same objects, the same class so to speak, but sometimes properties are added dynamically. Or in some cases you might see a property where the type is an int, but in edge cases a string. This function takes this into account.

P.S. It’s a proposal, so it’s always smart to do a double check.


function Propose-SQLTableDesign ($Collection) {

    $Props = @()
    foreach ($prop in $Collection[0].psobject.Properties.Name) {

        Write-Host "checking property : $prop" -fo green

        $unicode = $null
        $posDatetime = $null
        $posInt = $null
        $posDecimal = $null
        $proposed = $null

        $length = $Collection.$prop | ForEach-Object { $_.length } | Measure-Object -Min -Max

        if ($length.Maximum -eq 0) {
            $posDecimal = 'n/a'
            $posInt = 'n/a'
            $posDatetime = 'n/a'
            $proposed = 'n/a'
        } else {
            $posDecimal = -not $Collection.$prop.ForEach({ $_ -as [double] -is [double] -and $_ -as [double] -eq $_ }).contains($false)
            $posInt = -not $Collection.$prop.ForEach({ $_ -as [int] -is [int] -and $_ -as [int] -eq $_ }).contains($false)
            $posDatetime = -not $Collection.$prop.ForEach({ $_ -as [datetime] -is [datetime] }).contains($false)
        }

        if ($posDecimal -eq $true -and -not $proposed) {
            $wholePart = 0
            $decPart = 0
            $Collection.$prop | ForEach-Object {
                $ar = ([string]$_).split('.')
                $wholePart = $wholePart -gt $ar[0].length ? $wholePart : $ar[0].length
                $decPart = $decPart -gt $ar[1].length ? $decPart : $ar[1].length
            }
        } else {
            $wholePart = ''
            $decPart = ''
        }

        if (-not $posDecimal -and -not $posInt -and -not $posDatetime) {
            $unicode = ($Collection.$prop.foreach({ $_ -match '[^\x20-\x7F]' }).contains($true))
        }

        if (-not $proposed) {
            if ($posDatetime) {
                $proposed = 'DATETIME2'
            } elseif ($posInt) {
                $proposed = 'INT'
            } elseif ($posDecimal) {
                $proposed = "DECIMAL($($wholePart + $decPart),$($decPart))"
            } else {
                $unicodePrefix = $unicode ? 'N' : ''
                $typeFix = $length.Minimum -eq $length.Maximum ? 'CHAR' : 'VARCHAR'
                $lengthFix = "($($length.Maximum))"
                $proposed = "$unicodePrefix$typeFix$lengthFix"
            }
        }

        $Props += [PSCustomObject]@{
            Name      = $prop
            LengthMin = $length.Minimum
            LengthMax = $length.Maximum
            decimal   = $posDecimal
            int       = $posInt
            datetime  = $posDatetime
            WholePart = $wholePart
            DecPart   = $decPart
            Unicode   = $unicode
            Proposed  = $proposed
        }
    }

    $props
}
$collection = Get-ChildItem -Path ./
Propose-SQLTableDesign $collection | Format-Table

The results might look like this in case of Get-Item:

Name                LengthMin LengthMax decimal   int datetime WholePart DecPart Unicode Proposed
----                --------- --------- -------   --- -------- --------- ------- ------- --------
PSPath                     50        72   False False    False                     False VARCHAR(72)
PSParentPath               45        45   False False    False                     False CHAR(45)
PSChildName                 4        26   False False    False                     False VARCHAR(26)
PSDrive                     1         1   False False    False                     False CHAR(1)
PSProvider                  1         1   False False    False                     False CHAR(1)
PSIsContainer               1         1    True  True    False 5         0               INT
Mode                        5         5   False False    False                     False CHAR(5)
ModeWithoutHardLink         5         5   False False    False                     False CHAR(5)
BaseName                    4        26   False False    False                     False VARCHAR(26)
Target                      0         0     n/a   n/a      n/a                           n/a
LinkType                    0         0     n/a   n/a      n/a                           n/a
Parent                      1         1   False False    False                     False CHAR(1)
Root                        1         1   False False    False                     False CHAR(1)
FullName                   12        34   False False    False                     False VARCHAR(34)
Extension                   0         5   False False    False                     False VARCHAR(5)
Name                        4        26   False False    False                     False VARCHAR(26)
Exists                      1         1    True  True    False 4         0               INT
CreationTime                1         1   False False     True                           DATETIME2
CreationTimeUtc             1         1   False False     True                           DATETIME2
LastAccessTime              1         1   False False     True                           DATETIME2
LastAccessTimeUtc           1         1   False False     True                           DATETIME2
LastWriteTime               1         1   False False     True                           DATETIME2
LastWriteTimeUtc            1         1   False False     True                           DATETIME2
LinkTarget                  0         0     n/a   n/a      n/a                           n/a
Attributes                  1         1    True  True     True 9         0               DATETIME2