Create Database Diagrams with Powershell + yUML

Powershell MVP, Doug Finke has an interesting post demonstrating how to create UML class digrams from Powershell using the website yUML. So, I thought it would be a fun exercise to create a UML class diagram of database tables. It might seem a little odd to represent database tables in UML, however it can done and a few commerical allow you to do so. Basically tables are represented as classes, columns as attributes, constraints and indexes are behaviors and foreign key relationships are associations. For a good overview of how to model databases as UML class diagrams see Database Modelling in UML by By Geoffrey Sparks.
The yUML website generates UML through a simple syntax, where parameters are appended to the URL string (See Doug’s blog for an example). The hard part for generating class digrams from databases is getting the meta data about SQL Server tables in a usable format. In fact, the T-SQL query is much larger than the acommpanying Powershell code and also took me longer to figure out. The query sqlmeta.sql returns meta data about a SQL table and makes use of SQL 2005/2008 CTE’s and XPath. A single XML document is returned with column, primary key, constraint, index, trigger, and relationship information. For example using the sample AdventureWorks database and the Sales.Store table as parameters the following XML is returned:
      <column>PK CustomerID: int</column>
      <column>Name: nvarchar</column>
      <column>SalesPersonID: int</column>
      <column>Demographics: xml</column>
      <column>rowguid: uniqueidentifier</column>
      <column>ModifiedDate: datetime</column>
      <operation>FK: FK_Store_Customer_CustomerID</operation>
      <operation>FK: FK_Store_SalesPerson_SalesPersonID</operation>
      <operation>Index: AK_Store_rowguid</operation>
      <operation>Index: IX_Store_SalesPersonID</operation>
      <operation>Index: PXML_Store_Demographics</operation>
      <operation>PK: PK_Store_CustomerID</operation>
      <operation>Trigger: iStore</operation>
The Powershell script, yuml.ps1 includes function to execute a query and return a DataTable called Get-SqlData:
function Get-SqlData
    param([string]$serverName=$(throw ‘serverName is required.’), [string]$databaseName=$(throw ‘databaseName is required.’),
          [string]$query=$(throw ‘query is required.’))

    Write-Verbose "Get-SqlData serverName:$serverName databaseName:$databaseName query:$query"

    $connString = "Server=$serverName;Database=$databaseName;Integrated Security=SSPI;"
    $da = New-Object "System.Data.SqlClient.SqlDataAdapter" ($query,$connString)
    $dt = New-Object "System.Data.DataTable"
    $da.fill($dt) > $null

} #Get-SqlData

We’ll call the Get-SqlData function from Get-SqlMeta function and pass the lengthy T-SQL query as a parameter. Having obtained the SQL Server meta data, we need to extract the information to generate a URL string using the ConvertTo-yUML function.
function ConvertTo-yUML {
    param ([xml]$meta)

    $r = $meta.root.class | foreach {‘[‘ + $_.table + ‘|’}
    $table = $meta.root.class | foreach {‘[‘ + $_.table + ‘]’}

    $cols = $meta.root.class | foreach {$_.columns.column}
    $r += [string]::join(‘;’,$cols)

    $ops = $meta.root.class | foreach {$_.operations.operation}
    if ($ops)
    { $r += ‘|’ + [string]::join(‘;’,$ops) }
    $r += ‘]’ 

    $rels = $meta.root.class | foreach {$_.relations.relation}
    if ($rels)
    { $r +=  ",$table->" + [string]::join(",$table->",$rels) }


} #ConvertTo-yUML

Using the XML document above we should see the following output:
[Sales.Store|PK CustomerID: int;Name: nvarchar;SalesPersonID: int;Demographics: xml;rowguid: uniqueidentifier;ModifiedDate: datetime|FK: FK_Store_Customer_CustomerID;FK: FK_Store_SalesPerson_SalesPersonID;Index: AK_Store_rowguid;Index: IX_Store_SalesPersonID;Index: PXML_Store_Demographics;PK: PK_Store_CustomerID;Trigger: iStore],[Sales.Store]->[Sales.Customer],[Sales.Store]->[Sales.SalesPerson]
Finally we pass the ouput generated from ConvertTo-yUML to Doug’s original Get-yUMLDigram function:
Function Get-yUMLDiagram {
    $base = ""
    $address = $base + $yUML
    if($show) {
        $wc = New-Object Net.WebClient
        $wc.DownloadFile($address, $diagramFileName)
        Invoke-Item $diagramFileName 
    } else {

} #Get-yUMLDiagram

To use the script source, the functions and run the commands passing in server, database, schema and table name parameter. Here’s the completed example
. ./yuml.ps1
$serverName = ‘Z002SQLEXPRESS’;$databaseName = ‘AdventureWorks’;$schema=‘Sales’;$name=‘Store’
$meta = Get-SqlMeta $serverName $databaseName $schema $name
$yUML = (ConvertTo-yUML $meta[0])
Get-yUMLDiagram $yUML ‘C:Usersu00binstore.jpg’ -show
Running the code above produces the following output:
 **UPDATE: yUML now supports pdf output. I’ve updated my script using Doug’s revisions to output a pdf document. Here’s an example of pdf output:
Get-yUMLDiagram $yUML ‘C:Usersu00binstore.pdf’ -pdf
This entry was posted in PowerShell. Bookmark the permalink.