Last active
May 20, 2024 17:27
-
-
Save smileham/8188b3bb65f4ce1b645519db957db1c5 to your computer and use it in GitHub Desktop.
Powershell script to create a Salesforce Data Dictionary in CSV/Excel or JSON formats
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| # usage sfdd ALIAS | |
| # Needs ImportExcel Module installed | |
| # Needs sf cli installed | |
| # v1. First release | |
| # v2. Included Record Types | |
| # v3. Added PUML | |
| # v4. Add Ignore list (Json format {"ignoreObjects":["Object","Object2"],"ignorePackages":["package1"]}) | |
| # v5. Added support for BigER | |
| Param( | |
| [Parameter(Position=0,mandatory=$true)] | |
| [string] | |
| $alias = "", | |
| [bool] | |
| $includeAllObjects = $false, | |
| [bool] | |
| $generateTableCSV = $true, | |
| [bool] | |
| $generateGlobalCSV = $true, | |
| [bool] | |
| $generateXLS = $true, | |
| [bool] | |
| $includeMinCount = 0, | |
| [bool] | |
| $includePackages = $true, | |
| [bool] | |
| $includeMdtandCustomSettings = $false, | |
| [bool] | |
| $splitPackages = $true, | |
| [bool] | |
| $appendTimestamp = $true, | |
| [bool] | |
| $saveRawJSON = $true, | |
| [bool] | |
| $includeRecordTypes = $true, | |
| [bool] | |
| $generatePuml = $true, | |
| [string] | |
| $pumlLocation = "c:/apps/plantuml/plantuml.jar", | |
| [bool] | |
| $includeStdRelationsInPuml = $false, | |
| [string] | |
| $ignoreListLocation = "./ignore.json", | |
| [string] | |
| $pumlFormat = "pdf", | |
| [string] | |
| $generatePackageList = $true, | |
| [string] | |
| $generateBiger = $true | |
| ) | |
| function New-Row() { | |
| return [PSCustomObject]@{ | |
| sObject = $null | |
| label = $null | |
| apiName = $null | |
| isCustom = $null | |
| isAutoNumber = $null | |
| isFormula = $null | |
| isRequired = $null | |
| isUnique = $null | |
| isExternalId = $null | |
| helpText = $null | |
| type = $null | |
| length = $null | |
| defaultValue = $null | |
| formula = $null | |
| picklistValues = $null | |
| } | |
| } | |
| if ($appendTimestamp) { | |
| $timeStamp = "-" + [string](Get-Date -UFormat "%Y%m%d") | |
| } | |
| else { | |
| $timeStamp = "" | |
| } | |
| if (Test-Path -Path $ignoreListLocation){ | |
| $ignoreListFile = Get-Content $ignoreListLocation | Out-String | ConvertFrom-Json | |
| $ignoreObjectList = $ignoreListFile.ignoreObjects | |
| $ignorePackageList = $ignoreListFile.ignorePackages | |
| } | |
| else { | |
| $ignoreObjectList = @() | |
| $ignorePackageList = @() | |
| } | |
| $sheetNames = @{} | |
| $pumlPackages = @{} | |
| $sObjectsResponse = sf sobject list --sobject all -o $alias --json | convertFrom-Json | |
| if ($sObjectsResponse.status -eq 0) { | |
| mkdir -path "./$alias" | |
| if ($generateTableCSV -or $generateGlobalCSV) { | |
| mkdir -path "./$alias/CSV" | |
| } | |
| if ($generateXLS) { | |
| mkdir -path "./$alias/XLS" | |
| } | |
| if ($generatePuml) { | |
| mkdir -path "./$alias/PUML" | |
| if ($pumlLocation -ne "") { | |
| mkdir -path "./$alias/ERD" | |
| } | |
| } | |
| if ($generateBiger) { | |
| mkdir -path "./$alias/biger" | |
| } | |
| if ($saveRawJSON) { | |
| mkdir -path "./$alias/JSON" | |
| } | |
| $sObjects = $sObjectsResponse.result | Sort-Object | |
| foreach ($sObjectName in $sObjects) | |
| { | |
| $sObjectArray = @() | |
| $pumlEntity = "" | |
| $pumlRelationships = "" | |
| $bigerRelationships = "" | |
| Write-Debug "Checking $sObjectName" | |
| # Check for packages | |
| if ($sObjectName -match '^(.+)__(.+)__(.+)$') { | |
| $package = $Matches[1] | |
| if ($package -in $ignorePackageList -or -not($includePackages)) { | |
| Write-Debug "Ignoring Package $package" | |
| continue | |
| } | |
| } | |
| # Check for ignored objects | |
| if ($sObjectName -in $ignoreObjectList) { | |
| Write-Debug "Ignoring $sObjectName" | |
| continue | |
| } | |
| # Stop Custom Metadata (unless we include them) | |
| if ($sObjectName -Match "__mdt$" -and ((-not $includeMdtandCustomSettings) -or (-not $includeAllObjects))) { | |
| Write-Debug "Ignoring Settings" | |
| continue | |
| } | |
| # Check for "noisy" objects | |
| if ($sObjectName -match ".+(History|ChangeEvent|Feed|Share)$" -and (-not $includeAllObjects)) { | |
| Write-Debug "Ignoring Noisy $sObjectName" | |
| continue | |
| } | |
| # Get the Object via the Salesforce CLI | |
| $sObject = sf sobject describe --sobject $sObjectName -o $alias --json | convertFrom-Json | |
| # Stop Custom Settings (unless we include them) | |
| if ($sObject.result.customSetting -and ((-not $includeMdtandCustomSettings) -or (-not $includeAllObjects))) { | |
| Write-Debug "Ignoring Settings" | |
| continue | |
| } | |
| if ((($sObject.result.custom -or ($null -ne $sObject.result.urls.quickActions) -or ($sObject.result.fields -match "__c") -and ($null -eq $sObject.result.associateEntityType)) -or $includeAllObjects)) { | |
| # Check that there is at least X records in the system to include this object | |
| if ($includeMinCount -gt 0) { | |
| $soqlResult = sf data query --query "Select count() from $sObjectName" --target-org $alias --json | convertFrom-Json | |
| if (-not($soqlResult.result.totalSize -ge $includeMinCount)) { | |
| Write-Debug "Excluding $sObjectName" | |
| continue | |
| } | |
| } | |
| Write-Output "Writing $sObjectName" | |
| if ($saveRawJSON) { | |
| $sObject.result | ConvertTo-Json -depth 100 | Out-File "./$alias/JSON/$sObjectName$timestamp.json" | |
| } | |
| # Add Object Header | |
| $headerRow = New-Row | |
| $headerRow.sObject = $sObject.result.label + " Object" | |
| $headerRow.label = $sObject.result.label | |
| $headerRow.apiName = $sObject.result.name | |
| $sObjectArray += $headerRow | |
| $pumlEntity = "entity $sObjectName {`n" | |
| $bigerEntity = $pumlEntity | |
| $referenceList = @{} | |
| # Add Record Types | |
| if ($includeRecordTypes) { | |
| $pumlRecordTypes = "" | |
| $bigerRecordTypes = "" | |
| foreach ($recordType in $sObject.result.recordTypeInfos) { | |
| if ($recordType.active -and (-not $recordType.master)) { | |
| $recordTypeRow = New-Row | |
| $recordTypeRow.sObject = $sObject.result.label + " Record Type" | |
| $recordTypeRow.label = $recordType.name | |
| $recordTypeRow.apiName = $recordType.developerName | |
| $recordTypeRow.defaultValue = $recordType.defaultRecordTypeMapping | |
| $sObjectArray += $recordTypeRow | |
| if ($recordType.defaultRecordTypeMapping) { | |
| $pumlRecordTypes += "-" | |
| } | |
| $pumlRecordTypes += "RT: "+$recordType.developerName+"`n" | |
| if ($generateBiger) { | |
| $bigerRecordTypes += "entity "+$recordType.developerName+" extends "+$sObjectName+"{}`n" | |
| } | |
| } | |
| } | |
| $pumlEntity+=$pumlRecordTypes +"--`n" | |
| } | |
| # Add Fields | |
| $fields = $sObject.result.fields | Sort-Object -property custom,@{Expression="idlookup"; Descending=$true},name | |
| $pumlCustomLimit = $false | |
| foreach ($field in $fields) { | |
| if (-not $field.deprecatedAndHidden) { | |
| $ddRow = New-Row | |
| $ddRow.sObject = $sObject.result.label | |
| $ddRow.label = $field.label | |
| $ddRow.apiName = $field.name | |
| $ddRow.isCustom = $field.custom | |
| $ddRow.isAutoNumber = $field.autoNumber | |
| $ddRow.isFormula = $field.calculated | |
| $ddRow.isRequired = -not($field.nillable) | |
| $ddRow.isUnique = $field.unique | |
| $ddRow.isExternalId = $field.externalId | |
| $ddRow.helpText = $field.inlineHelpText | |
| $ddRow.type = $field.type | |
| $ddRow.length = $field.length -gt 0 ? $field.length:($field.digits -gt 0 ? $field.digits : ($field.precision -gt 0 ? $field.precision : $null)) | |
| $ddRow.defaultValue = $field.defaultValue | |
| $ddRow.formula = $field.calculatedFormula | |
| $ddRow.picklistValues = "" | |
| if ($null -ne $field.extraTypeInfo) { | |
| $ddRow.type += " ("+$field.extraTypeInfo+")" | |
| } | |
| $pumlTags = "" | |
| $bigerTags = "" | |
| if ($ddRow.isCustom -and (-not $pumlCustomLimit)) { | |
| $pumlEntity+="..`n" | |
| $pumlCustomLimit = $true | |
| } | |
| if ($ddRow.isRequired) { | |
| $pumlEntity += "* " | |
| } | |
| if ($ddRow.apiName -eq "Id") { | |
| $pumlTags += " <<PK>>" | |
| $bigerTags += " key" | |
| } | |
| if ($ddRow.isAutoNumber) { | |
| $pumlTags += " <<generated>>" | |
| } | |
| foreach ($picklistValue in $field.picklistValues) { | |
| $ddRow.picklistValues += $picklistValue.label + ";" | |
| } | |
| if ($ddRow.type -eq "reference") { | |
| $reference = $field.referenceTo[0] | |
| $ddRow.type = $ddRow.type+" ("+$reference+")" | |
| if (($generatePuml -or $generateBiger)-and (($reference -notmatch "User|Group|RecordType|Organization") -or $includeStdRelationsInPuml) -and ($referenceList[$reference] -ne 1) -and (-not($reference -in $ignoreObjectList))) { | |
| $ignore = $false | |
| if ($reference -match '^(.+)__(.+)__(.+)$') { | |
| $package = $Matches[1] | |
| if ($package -in $ignorePackageList) { | |
| $ignore = $true | |
| } | |
| } | |
| if (-not $ignore) { | |
| if ($generatePuml) { | |
| $pumlRelationships += "$sObjectName }o--o| $reference`n" | |
| $referenceList[$reference] = 1; | |
| } | |
| if ($generateBiger) { | |
| $bigerRelationships += "relationship "+$sObjectName+"_"+$ddRow.apiName+" {"+$reference+"[1] -> "+$sObjectName+"[N]}`n" | |
| } | |
| } | |
| } | |
| $pumlTags+= " <<FK>>" | |
| } | |
| else { | |
| $bigerEntity += "`t"+$ddRow.apiName+" : "+$field.type+$bigerTags+"`n" | |
| } | |
| if ($ddRow.isExternalId) { | |
| $pumlTags+= " <<FK>>" | |
| } | |
| $pumlType = "" | |
| if ($ddRow.length -gt 0) { | |
| $pumlType = $ddRow.type+"("+$ddRow.length+")" | |
| } | |
| else { | |
| $pumlType = $ddRow.type | |
| } | |
| if ($ddRow.isFormula) { | |
| $pumlType = "Formula("+$pumlType+")" | |
| } | |
| $pumlEntity += "`t"+$ddRow.apiName+" : "+$pumlType+$pumlTags+"`n" | |
| $ddRow.picklistValues = $ddRow.picklistValues.Trim(';') | |
| $sObjectArray+=$ddRow | |
| } | |
| } | |
| if ($generatePuml) { | |
| $pumlEntity += "}`n" | |
| if ($sObjectName -match '^(.+)__(.+)__(.+)$') { | |
| $package = $Matches[1] | |
| $pumlPackages[$package] += 1 | |
| Add-Content -Path "./$alias/PUML/$alias-$package$timeStamp.puml" -value $pumlEntity | |
| } | |
| else { | |
| Add-Content -Path "./$alias/PUML/$alias-Objects$timeStamp.puml" -value $pumlEntity | |
| } | |
| if ($pumlRelationships -ne "") { | |
| Add-Content -Path "./$alias/PUML/$alias-Relationships$timeStamp.puml" -value $pumlRelationships | |
| } | |
| } | |
| if ($generateBiger) { | |
| $bigerEntity +="}`n" | |
| Add-Content -Path "./$alias/biger/$alias-Objects$timeStamp.erd" -value $bigerEntity | |
| Add-Content -Path "./$alias/biger/$alias-Relationships$timeStamp.erd" -value $bigerRelationships | |
| if ($bigerRecordTypes -ne "") { | |
| Add-Content -Path "./$alias/biger/$alias-RecordTypes$timeStamp.erd" -value $bigerRecordTypes | |
| } | |
| } | |
| if ($generateTableCSV) { | |
| $sObjectArray | Export-Csv -Path "./$alias/CSV/$sObjectName$timeStamp.csv" | |
| } | |
| if ($generateXLS) { | |
| if ($sObjectName.length -gt 30) { | |
| $truncatedName = $sObjectName.Substring(0,30) | |
| if ($sheetNames[$truncatedName] -gt 0) { | |
| $sheetNames[$truncatedName] += 1 | |
| } | |
| else { | |
| $sheetNames[$truncatedName] = 1 | |
| } | |
| $worksheetName = $truncatedName+$sheetNames[$truncatedName] | |
| Write-Debug "Truncated to $worksheetName" | |
| } | |
| else { | |
| $worksheetName = $sObjectName | |
| } | |
| if ($splitPackages -and ($sObjectName -match '^(.+)__(.+)__(.+)$')) { | |
| $package = $Matches[1] | |
| $sObjectArray | Export-Excel -path "./$alias/XLS/$alias-$package$timeStamp.xlsx" -Worksheetname $worksheetName | |
| } | |
| else { | |
| $sObjectArray | Export-Excel -path "./$alias/XLS/$alias$timeStamp.xlsx" -Worksheetname $worksheetName | |
| } | |
| } | |
| if ($generateGlobalCSV) { | |
| if ($splitPackages -and ($sObjectName -match '^(.+)__(.+)__(.+)$')) { | |
| $package = $Matches[1] | |
| $sObjectArray | Export-Csv -Path "./$alias/CSV/$alias-$package$timeStamp.csv" -append | |
| } | |
| else { | |
| $sObjectArray | Export-Csv -Path "./$alias/CSV/$alias$timeStamp.csv" -append | |
| } | |
| } | |
| } | |
| } | |
| if ($generatePuml) { | |
| $pumlPackageList = "" | |
| foreach ($package in $pumlPackages.keys) { | |
| $pumlPackageList+= "package $package {`n" | |
| $pumlPackageList+= "!include ./$alias-$package$timeStamp.puml`n" | |
| $pumlPackageList+= "}`n" | |
| } | |
| $pumlHeader = "@startuml`nheader`n$alias$timeStamp`nendheader`nscale 32768 width`nhide circle`nskinparam linetype polyline`n!include ./$alias-Objects$timeStamp.puml`n" | |
| $pumlFooter = "!include ./$alias-Relationships$timeStamp.puml`n@enduml" | |
| Add-Content -Path "./$alias/PUML/$alias$timeStamp.puml" -value $pumlHeader | |
| Add-Content -Path "./$alias/PUML/$alias$timeStamp.puml" -value $pumlPackageList | |
| Add-Content -Path "./$alias/PUML/$alias$timeStamp.puml" -value $pumlFooter | |
| if($pumlLocation -ne "") { | |
| if (Test-Path -Path $pumlLocation){ | |
| $execute = "java -DPLANTUML_LIMIT_SIZE=32768 -jar $pumlLocation ./$alias/PUML/$alias$timeStamp.puml -o ../ERD/ -$pumlFormat" | |
| try { | |
| Write-Debug "Generating ERD" | |
| Invoke-Expression $execute | |
| } | |
| catch { | |
| Write-Error "Issue with PlantUML" | |
| } | |
| } | |
| else { | |
| Write-Error "Can't find PlantUML" | |
| } | |
| } | |
| } | |
| if ($generateBiger) { | |
| $biger = "erdiagram $alias`nnotation=default`n" | |
| $bigerEntities = Get-Content -Path ./$alias/biger/$alias-Objects$timeStamp.erd -Raw | |
| $bigerAllRelationships = Get-Content -Path ./$alias/biger/$alias-Relationships$timeStamp.erd -Raw | |
| $bigerAllRecordTypes = Get-Content -Path ./$alias/biger/$alias-RecordTypes$timeStamp.erd -Raw | |
| $biger += $bigerEntities; | |
| $biger += $bigerAllRelationships; | |
| $biger += $bigerAllRecordTypes; | |
| Add-Content -Path "./$alias/biger/$alias$timeStamp.erd" -value $biger | |
| } | |
| if ($generatePackageList) { | |
| $packageQuery = "SELECT Id, SubscriberPackageId, SubscriberPackage.NamespacePrefix, SubscriberPackage.Name, SubscriberPackageVersion.Id, SubscriberPackageVersion.Name, SubscriberPackageVersion.MajorVersion,SubscriberPackageVersion.MinorVersion,SubscriberPackageVersion.PatchVersion, SubscriberPackageVersion.BuildNumber FROM InstalledSubscriberPackage ORDER BY SubscriberPackageId" | |
| $soqlResult = sf data query --query $packageQuery --target-org $alias -t --result-format csv > ./$alias/$alias-packages$timestamp.csv | |
| } | |
| } | |
| else { | |
| Write-Error "Issue with SF CLI" | |
| } |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment