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-TableThe 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