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.

 1function Propose-SQLTableDesign ($Collection) {
 2
 3    $Props = @()
 4    foreach ($prop in $Collection[0].psobject.Properties.Name) {
 5
 6        Write-Host "checking property : $prop" -fo green
 7
 8        $unicode = $null
 9        $posDatetime = $null
10        $posInt = $null
11        $posDecimal = $null
12        $proposed = $null
13
14        $length = $Collection.$prop | ForEach-Object { $_.length } | Measure-Object -Min -Max
15
16        if ($length.Maximum -eq 0) {
17            $posDecimal = 'n/a'
18            $posInt = 'n/a'
19            $posDatetime = 'n/a'
20            $proposed = 'n/a'
21        } else {
22            $posDecimal = -not $Collection.$prop.ForEach({ $_ -as [double] -is [double] -and $_ -as [double] -eq $_ }).contains($false)
23            $posInt = -not $Collection.$prop.ForEach({ $_ -as [int] -is [int] -and $_ -as [int] -eq $_ }).contains($false)
24            $posDatetime = -not $Collection.$prop.ForEach({ $_ -as [datetime] -is [datetime] }).contains($false)
25        }
26
27        if ($posDecimal -eq $true -and -not $proposed) {
28            $wholePart = 0
29            $decPart = 0
30            $Collection.$prop | ForEach-Object {
31                $ar = ([string]$_).split('.')
32                $wholePart = $wholePart -gt $ar[0].length ? $wholePart : $ar[0].length
33                $decPart = $decPart -gt $ar[1].length ? $decPart : $ar[1].length
34            }
35        } else {
36            $wholePart = ''
37            $decPart = ''
38        }
39
40        if (-not $posDecimal -and -not $posInt -and -not $posDatetime) {
41            $unicode = ($Collection.$prop.foreach({ $_ -match '[^\x20-\x7F]' }).contains($true))
42        }
43
44        if (-not $proposed) {
45            if ($posDatetime) {
46                $proposed = 'DATETIME2'
47            } elseif ($posInt) {
48                $proposed = 'INT'
49            } elseif ($posDecimal) {
50                $proposed = "DECIMAL($($wholePart + $decPart),$($decPart))"
51            } else {
52                $unicodePrefix = $unicode ? 'N' : ''
53                $typeFix = $length.Minimum -eq $length.Maximum ? 'CHAR' : 'VARCHAR'
54                $lengthFix = "($($length.Maximum))"
55                $proposed = "$unicodePrefix$typeFix$lengthFix"
56            }
57        }
58
59        $Props += [PSCustomObject]@{
60            Name      = $prop
61            LengthMin = $length.Minimum
62            LengthMax = $length.Maximum
63            decimal   = $posDecimal
64            int       = $posInt
65            datetime  = $posDatetime
66            WholePart = $wholePart
67            DecPart   = $decPart
68            Unicode   = $unicode
69            Proposed  = $proposed
70        }
71    }
72
73    $props
74}
75$collection = Get-ChildItem -Path ./
76Propose-SQLTableDesign $collection | Format-Table

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

 1Name                LengthMin LengthMax decimal   int datetime WholePart DecPart Unicode Proposed
 2----                --------- --------- -------   --- -------- --------- ------- ------- --------
 3PSPath                     50        72   False False    False                     False VARCHAR(72)
 4PSParentPath               45        45   False False    False                     False CHAR(45)
 5PSChildName                 4        26   False False    False                     False VARCHAR(26)
 6PSDrive                     1         1   False False    False                     False CHAR(1)
 7PSProvider                  1         1   False False    False                     False CHAR(1)
 8PSIsContainer               1         1    True  True    False 5         0               INT
 9Mode                        5         5   False False    False                     False CHAR(5)
10ModeWithoutHardLink         5         5   False False    False                     False CHAR(5)
11BaseName                    4        26   False False    False                     False VARCHAR(26)
12Target                      0         0     n/a   n/a      n/a                           n/a
13LinkType                    0         0     n/a   n/a      n/a                           n/a
14Parent                      1         1   False False    False                     False CHAR(1)
15Root                        1         1   False False    False                     False CHAR(1)
16FullName                   12        34   False False    False                     False VARCHAR(34)
17Extension                   0         5   False False    False                     False VARCHAR(5)
18Name                        4        26   False False    False                     False VARCHAR(26)
19Exists                      1         1    True  True    False 4         0               INT
20CreationTime                1         1   False False     True                           DATETIME2
21CreationTimeUtc             1         1   False False     True                           DATETIME2
22LastAccessTime              1         1   False False     True                           DATETIME2
23LastAccessTimeUtc           1         1   False False     True                           DATETIME2
24LastWriteTime               1         1   False False     True                           DATETIME2
25LastWriteTimeUtc            1         1   False False     True                           DATETIME2
26LinkTarget                  0         0     n/a   n/a      n/a                           n/a
27Attributes                  1         1    True  True     True 9         0               DATETIME2