Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- 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
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement