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