MakeERD.ps1

param(
    $SqlInstance = 'SQLSRV01',
    $Database = "AdventureWorks2017"
)

import-module dbatools;

if ( $SqlInstance.GetType() -ne 'Server' ) {
    $inst = Connect-DbaInstance -SqlInstance $SqlInstance.ToString()
} 
else {
    $inst = $SqlInstance;
}


$db = Get-DbaDatabase -SqlInstance $inst -Database $Database

$colors = @(
      "#62A9FF", "#06DCFB", "#01FCEF", "#03EBA6", "#01F33E", "#62D0FF"
    , "#75B4FF", "#24E0FB", "#1FFEF3", "#03F3AB", "#0AFE47", "#75D6FF"
    , "#86BCFF", "#3DE4FC", "#5FFEF7", "#33FDC0", "#4BFE78", "#8ADCFF"
    , "#99C7FF", "#63E9FC", "#74FEF8", "#62FDCE", "#72FE95", "#99E0FF"
    , "#99C7FF", "#75ECFD", "#92FEF9", "#7DFDD7", "#8BFEA8", "#A8E4FF"
    , "#A8CFFF", "#8CEFFD", "#A5FEFA", "#8FFEDD", "#A3FEBA", "#BBEBFF"
    , "#BBDAFF", "#ACF3FD", "#B5FFFC", "#A5FEE3", "#B5FFC8", "#CEF0FF"
    , "#D0E6FF", "#C0F7FE", "#CEFFFD", "#BEFEEB", "#CAFFD8", "#D9F3FF"
    , "#DBEBFF", "#C0F7FE", "#E1FFFE", "#BDFFEA", "#EAFFEF", "#ECFAFF"
    , "#ECF4FF", "#E6FCFF", "#F2FFFE", "#CFFEF0", "#EAFFEF", "#F9FDFF"
    , "#F9FCFF", "#F9FEFF", "#FDFFFF", "#F7FFFD", "#F9FFFB", "#FDFEFF"
);

function Get-TblDataType {
    <#
        .SYNOPSIS
    
        .DESCRIPTION
    
        .PARAMETER ComputerName
    
        .EXAMPLE
    
        .INPUTS
      
        .OUTPUTS
    #>
    [CmdletBinding()]
    Param(
      [Parameter(ValueFromPipeline=$True,
          Mandatory=$True,
      ValueFromPipelineByPropertyName=$True)]
      [Microsoft.SqlServer.Management.Smo.Column]
      $Column
    )
  
    Begin{}
  
    Process{
        $dt = $Column.DataType.ToString();
        switch -regex ($Column.DataType.ToString()) {
            "(.*)char" { 
                $dt += "(" ;
                if ( $Column.DataType.MaximumLength -eq "-1") {
                    $dt += "MAX" ;
                }
                else {
                    $dt += "$($Column.DataType.MaximumLength)" ;
                }
                $dt += ")" ;
                break;
            } 
            "datetime2" { $dt += "(" + $Column.DataType.NumericScale.ToString() + ")"; }
            "decimal" { $dt += "($($Column.DataType.NumericPrecision),$($Column.DataType.NumericScale))"; }

        }

        return $dt
    }
  
    End {}
  }


[string]$uml = '';
$uml += "`n@startuml`nskinparam linetype ortho`nhide circle`nskinparam roundcorner 20"

[int]$ColorIndex = 0;
$db.Tables | Group-Object -Property Schema | %{
    # $uml += "`nnamespace $($_.Name) {"
    $SchemaColor = $colors[$ColorIndex];
    $ColorIndex++;
     $_.Group | %{  
        $tbl = $_;

        $uml += "`nEntity $($tbl.Schema).$($tbl.Name) $SchemaColor  {"

        # Key Columns
        $tbl.Columns | ?{ $_.InPrimaryKey } | %{

            $uml += "`n`t* $($_.Name) : $( Get-TblDataType $_ )"
        }

        $uml += "`n`t--"

        #Non-Key Columns
        $tbl.Columns | ?{ -Not $_.InPrimaryKey } | %{
            $uml += "`n`t$(if ( $_.Nullable ){ '* ' } else { '  ' })$($_.Name) : $( Get-TblDataType $_ )"
        }

        $uml += "`n}"


    }
    # $uml += "`n}"
} #Group

#Relationships
$db.Tables | ?{$_.ForeignKeys.Count -gt 0} | %{
    $tbl = $_;
    $tbl.ForeignKeys | %{
        $uml += "`n$($tbl.Schema).$($tbl.Name) - $($_.ReferencedTableSchema).$($_.ReferencedTable)"
    }
    
}

#Layout - Hidden links to try and stretch out the layout
$uml += "`n`n'Add hidden links to force the model to stretch out"
for ($i = 0; $i -lt [int]($db.Tables.Count / 5); $i++ ){
    # Pick a random table
    $t = $db.Tables[(Get-Random -Minimum 0 -Maximum $db.Tables.Count)];

    # Link to other random tables
    foreach($j in 1..10) {
        $r = $db.Tables[(Get-Random -Minimum 0 -Maximum $db.Tables.Count)];
        $uml += "`n$($t.Schema).$($t.Name) -[hidden]- $($r.Schema).$($r.Name)"
    }
}


$uml += "`n@enduml"
Set-Content -Path "./db.puml" -Value $uml