{"id":2204,"date":"2016-05-03T00:00:00","date_gmt":"2016-04-15T00:00:00","guid":{"rendered":"https:\/\/test.simple-talk.com\/uncategorized\/automatically-creating-uml-database-diagrams-for-sql-server\/"},"modified":"2026-03-17T20:25:20","modified_gmt":"2026-03-17T20:25:20","slug":"automatically-creating-uml-database-diagrams-for-sql-server","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/tools-sql-server\/automatically-creating-uml-database-diagrams-for-sql-server\/","title":{"rendered":"SQL to UML: Auto-Generate Database Diagrams with PlantUML"},"content":{"rendered":"
\n

This article shows how to automatically generate UML database diagrams from SQL Server metadata using only open-source tools. The workflow: a T-SQL script queries sys.tables, sys.columns, sys.foreign_keys, and related metadata to generate PlantUML text, which PlantUML (using GraphViz) renders as PNG or SVG diagrams. The approach produces class diagrams (tables with columns and types), ER diagrams (showing foreign key relationships), and even sequence diagrams – all refreshable by re-running the script after database changes. No manual drawing required.<\/p>\n

Introduction<\/h2>\n

Diagrams are often a great help in understanding how databases work. I’ve always wanted to generate various database diagrams automatically to go with my database documentation. The problem is that, once you’ve drawn them nicely in a drawing package, you feel reluctant to alter the database because you’d then have to re-draw the diagrams to reflect those changes. This article explains a way of creating easily ‘refreshed’ automated diagrams based only on open-source, or free, tools.<\/p>\n

PlantUML<\/h1>\n

We’ll be using PlantUML. PlantUML is an open-source project for writing Unified Modelling Language (UML) diagrams for Sequence, Use-Case, Class, Activity, Component, State and Objects. It uses GraphViz for its graphical output. Its use, like GraphViz, is to render quite complex diagrams from a simple intuitive language based on text.<\/p>\n

For example, this will give you a simple UML sequence diagram:<\/p>\n

@startuml\nactor Phil #red\nactor Editor #green\nPhil -> Editor: suggestion for an article\nEditor --> Phil: wary response requesting details\nPhil -> Editor: submits some pretty pictures\nEditor --> Phil: Reluctant agreement\n@enduml\n<\/pre>\n
If this code is then processed by PlantUML, would provide this .PNG image:<\/p>\n
\"2405-clip_image002.jpg\"<\/p>\n
Don’t like the style? You can specify it how you like. With a little extra tweaking with the skinparam<\/code>, you can get this instead<\/p>\n
\"2405-handwritten-diagram.png\"<\/p>\n
the code changes are simple<\/p>\n
\t@startuml\nskinparam handwritten true\nskinparam monochrome true\nskinparam packageStyle rect\nskinparam defaultFontName Buxton Sketch\nskinparam shadowing false\n\nactor Phil #black\nactor Editor #grey\nPhil -> Editor: Is the style a bit dated?\nEditor --> Phil: Yes, it is a bit nineties\n\nPhil -> Editor: How about a more handwritten 'arty' look?\nEditor --> Phil: Sure, how about this?\n\n@enduml\n<\/pre>\n
Although you may have little urge to use UML diagrams in your everyday work, PlantUML is versatile enough to provide for a range of requirements. For a relational database person who winces at the terms ‘class’ and ‘persistence’, even UML modelling has its uses as a way of communicating ideas and designs.<\/p>\n
In this article I’m going to show how it can be used to easily generate class diagrams for database objects. I will use TSQL code to create the PlantUML code directly.<\/p>\n
Getting up and running<\/h1>\n
To get you started, there is a little browser application here<\/a> on the PlantUML site\u00a0 that allows you to type in PlantUML code and view the results. With that, and the PlantUML manual, you should be well away with using this interesting application. Chrome has an Add-in that runs PlantUML called the UML Diagram Editor.<\/p>\n
There are many ways of using PlantUML. There are several Atom packages that add PlantUML integration. If you use DocuWiki for documentation, there is an add-in for PlantUML, which is handy for teams since there is nothing to install on your machine then. If you want to use PlantUML from the command-line, there are install instructions here<\/a>. I use AsciiDocFx, which installs the prerequisites for you (though you may need to set the path to your GraphViz install. You can use your favourite editor via a command-line interface as well. PlantUML uses simple text-based instructions to render UML diagrams and these can be generated from SQL to show you such things as the intricacies of your database permission system, the foreign-key dependencies, or the details of your indexing strategy for a table.<\/p>\n
We’ll just stick to showing a subset of your database objects and the dependencies between them.<\/p>\n
Data Modelling With Unified Modelling Language (UML)<\/h1>\n
Let’s look at AdventureWorks, just so we can try things out. I’ve always been caught out by views so it would be nice to see those objects in the database that a view references. Here is a simple diagram.<\/p>\n
\"2405-clip_image004.jpg\"<\/p>\n
Note that schemas have been represented as folders. All I did to do this was to use the text:<\/p>\n
@startUML\n\u00a0\nhide empty members\nhide circle\nskinparam classarrowcolor gray\n\u00a0\nHumanResources.vEmployeeDepartmentHistory --|> HumanResources.Department:References\nHumanResources.vEmployeeDepartmentHistory --|> HumanResources.Employee:References\nHumanResources.vEmployeeDepartmentHistory --|> HumanResources.EmployeeDepartmentHistory:References\nHumanResources.vEmployeeDepartmentHistory --|> Person.Person:References\nHumanResources.vEmployeeDepartmentHistory --|> HumanResources.Shift:References\n\u00a0\n@enduml\n\u00a0\n<\/pre>\n
I didn’t type this in. I just executed the SQL query that finds the soft references and foreign key references to and from the object that you name…<\/p>\n
DECLARE @object_ID INT\nSELECT @Object_ID=object_id('HumanResources.vEmployeeDepartmentHistory')\nSELECT coalesce(object_schema_name(referencing_ID)+'.','')\n\u00a0\u00a0\u00a0\u00a0 + object_name(referencing_ID) +' --|> '\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0  + referenced_schema_name+'.'+Referenced_Entity_name\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0  + ':References' \n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0\u00a0\u00a0 --AS reference\nFROM sys.sql_expression_dependencies\n\u00a0\u00a0\u00a0 WHERE (referencing_id =@object_ID\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0  OR referenced_ID = @object_ID)\n\u00a0\u00a0\u00a0\u00a0\u00a0 AND is_schema_bound_reference =0 \n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0  and referenced_ID is not null\nUNION ALL\nSELECT coalesce(object_schema_name(parent_object_ID)+'.','') \n\u00a0\u00a0\u00a0 + object_name(parent_object_ID) + ' --|> '\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 + coalesce(object_schema_name(referenced_object_ID)+'.','')\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 + object_name(referenced_object_ID)+':FK' \nFROM sys.foreign_keys\n\u00a0\u00a0\u00a0 WHERE parent_object_ID = @object_ID\n\u00a0\u00a0\u00a0\u00a0\u00a0 OR referenced_object_ID = @object_ID\n\u00a0\n<\/pre>\n
…and copied the result from the results pane (use text mode and set the ‘Query<\/i>‘ ‘Options<\/i>‘ -> ‘Results’ -> ‘Text’ -> ‘Maximum number of characters displayed in each column<\/i>‘ to 8192 or some other generous figure)<\/p>\n
…but you can of course use it with any other database object such as dbo.ufnGetContactInformation. <\/b> Obviously, there is plenty more you can include but there is something to be said for having just one clear message to every diagram. <\/b>Be wary about including too much in a diagram. Any network diagram quickly morphs into a birds-nest diagram if you try to do too much. This type of diagram was never intended to provide an entire map of your database.<\/p>\n
Once you’ve got some confidence, you can use PowerShell to update diagrams automatically. You just execute the code, save the result to disk, and pass the result to PlantUML, which has a command-line interface.<\/p>\n
Sometimes we would like to do a bit more than this. Now UML never had a stereotype to deal with a relational database, but there  is an established database profile<\/a> that includes a logical and physical model. As I have no strong feelings about the way that a table is represented, I’ve not deviated much from the standard class.<\/p>\n
\"2405-clip_image006.jpg\"<\/p>\n
We have drilled in to the detail from the first diagram here to look more closely at the tables. PlantUML is able to give you immense diagrams which are probably useless for our purpose here. If you want to do this, I’d advise you just to open the wallet a bit and use SQL Dependency Tracker instead. Here we want to keep things simpler, and just want to document tables, views, and functions and their dependencies in detail.<\/p>\n
The PlantUML source code that we used is this, generated from SQL code…<\/p>\n
!define table(x) class x << (T,mistyrose) >> \n!define view(x) class x << (V,lightblue) >> \n!define table(x) class x << (T,mistyrose) >> \n!define tr(x) class x << (R,red) >> \n!define tf(x) class x << (F,darkorange) >> \n!define af(x) class x << (F,white) >> \n!define fn(x) class x << (F,plum) >> \n!define fs(x) class x << (F,tan) >> \n!define ft(x) class x << (F,wheat) >> \n!define if(x) class x << (F,gaisboro) >> \n!define p(x) class x << (P,indianred) >> \n!define pc(x) class x << (P,lemonshiffon) >> \n!define x(x) class x << (P,linen) >>\n\u00a0\nhide methods \nhide stereotypes \nskinparam classarrowcolor gray\n\u00a0\ntable(HumanResources.Employee) {\nBusinessEntityID: int <<pk>> <<fk>>\nNationalIDNumber: nvarchar\nLoginID: nvarchar\nOrganizationNode: hierarchyid\nOrganizationLevel: smallint\nJobTitle: nvarchar\nBirthDate: date\nMaritalStatus: nchar\nGender: nchar\nHireDate: date\nSalariedFlag: Flag\nVacationHours: smallint\nSickLeaveHours: smallint\nCurrentFlag: Flag\nrowguid: uniqueidentifier\nModifiedDate: datetime\n__ trigger __\ndEmployee\n}\ntable(Person.Person) {\nBusinessEntityID: int <<pk>> <<fk>>\nPersonType: nchar <<fk>>\nNameStyle: NameStyle\nTitle: nvarchar\nFirstName: Name\nMiddleName: Name\nLastName: Name\nSuffix: nvarchar\nEmailPromotion: int\nAdditionalContactInfo: xml\nDemographics: xml\nrowguid: uniqueidentifier\nModifiedDate: datetime\n__ trigger __\niuPerson\n}\nview(HumanResources.vEmployeeDepartmentHistory) {\nBusinessEntityID: int\nTitle: nvarchar\nFirstName: Name\nMiddleName: Name\nLastName: Name\nSuffix: nvarchar\nShift: Name\nDepartment: Name\nGroupName: Name\nStartDate: date\nEndDate: date\n}\ntf(dbo.ufnGetContactInformation) {\n@PersonID: int\n}\ndbo.ufnGetContactInformation -|> HumanResources.Employee:References\ndbo.ufnGetContactInformation -|> Person.Person:References\nHumanResources.vEmployeeDepartmentHistory -|> HumanResources.Employee:References\nHumanResources.vEmployeeDepartmentHistory -|> Person.Person:References\nHumanResources.Employee -|> Person.Person:FK\n\u00a0\n<\/pre>\n
All we have done is to give a list of the tables that we want a diagram for.<\/p>\n
Here is the code:<\/p>\n
Declare @ThePlantUMLCode Varchar(max)\nexecute CreatePlantUMLCode @ObjectsToShow='HumanResources.employee person.person HumanResources.vEmployeeDepartmentHistory dbo.ufnGetContactInformation',\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 @MyPlantUMLStatement=@ThePlantUMLCode output\nselect @ThePlantUMLCode\n\u00a0\n<\/pre>\n
I have only shown how to execute the stored procedure I wrote. I have specified the four objects whose dependencies and relationships I’m interested in. You may want to see all the dependencies and relationship of any object but even just the directly linked objects can overwhelm the diagram. It is better to be selective. PlantUML will do its best to oblige but you are soon at the wallchart scale of activity.<\/p>\n
This stored procedure can do other things. What if you want to see a UML diagram of all the objects that reference person.address Here, it is<\/p>\n
Declare @ThePlantUMLCode Varchar(max)\nexecute CreatePlantUMLCode @DependsOn='person.address',\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 @MyPlantUMLStatement=@ThePlantUMLCode output\nselect @ThePlantUMLCode\n<\/pre>\n
…giving you this, once the output is passed to PlantUML. …<\/p>\n
\"2405-clip_image008.jpg\"<\/p>\n
… or this …<\/p>\n
Declare @ThePlantUMLCode Varchar(max)\nexecute CreatePlantUMLCode @DependenciesOf='person.address',\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 @MyPlantUMLStatement=@ThePlantUMLCode output\nselect @ThePlantUMLCode\nThePlantUMLCode\n<\/pre>\n
… giving you this … \"2405-clip_image009.png\"<\/p>\n
I’ve given the SQL code at the end of the article.<\/p>\n
Modifying your diagrams<\/h1>\n
In dependencies and references in SQL Server<\/a> and SQL naming conventions for readable diagrams<\/a>. I have taken quite a few design decisions which you may not like. Fortunately, PlantUML has an excellent PDF manual which explains that you can change almost everything. It is so good that I’m not going to attempt to repeat anything from it. However, be assured that diagrams can be altered to suit your requirements: Related objects can be placed side by side, or vertically;, You can use dotted arrows or arrows with a different head; You can opt to avoid shadows, and change the colour of anything. My instinct is to leave GraphViz and PlantUML to do as much as possible by default.<\/p>\n
PlantUML supports a flavour of markdown called Creole. This allows you to have formatted notes and descriptions. There is even a full set of icons from OpenIconic. Because PlantUML is based on GraphViz, you can use Graphviz raw. This allows you to do more general network visualisation diagrams for general software-engineering purposes. For database people, it is great for plotting out chains of dependencies in database data or in databases themselves.<\/p>\n
GraphViz Dot Language<\/h1>\n
PlantUML can execute graphviz dot language files as well. We can therefore use PlantUML to draw ‘network’ diagrams, which is something thet GraphViz does extraordinarily well. Most of the time, you’d probably want to do it with data but you can do it for your SQL Server metadata. Here, for example is Northwind’s table dependency diagram based on foreign key relationships<\/p>\n
@startdot\ndigraph G { \nCustomerCustomerDemo -> CustomerDemographics;\nCustomerCustomerDemo -> Customers;\nEmployees -> Employees;\nEmployeeTerritories -> Employees;\nEmployeeTerritories -> Territories;\nOrder Details -> Orders;\nOrder Details -> Products;\nOrders -> Customers;\nOrders -> Employees;\nOrders -> Shippers;\nProducts -> Categories;\nProducts -> Suppliers;\nTerritories -> Region;} \n@enddot\n\n<\/pre>\n
This code produced this diagram (you can click on it to enlarge it)<\/p>\n
 \"2405-clip_image011.png\"<\/a><\/p>\n
..but you’ll see that I’m cheating a bit by using a very small sample database. Even AdventureWorks produces a big mess.<\/p>\n
 \"2405-clip_image013.png\"<\/a><\/p>\n
Which you can view if you have an image viewer that scrolls!<\/p>\n
\"2405-clip_image015.jpg\"<\/p>\n
How did I do this? Just the same way as the first database UML diagrams. I just executed this in SSMS, and copied the result into a text editor, just to put in the top and tail as shown above.<\/p>\n
Select distinct\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 object_name(parent_object_ID) + ' -> '\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 + object_name(referenced_object_ID)+';'\nFROM sys.foreign_keys\n<\/pre>\n
This can easily be refined into a stored procedure that takes out those illegal characters that make graphviz crash. GraphViz has a lot of power and it is worth looking around for examples and information.
Read also: <\/strong>Visualizing graph tables with TGF<\/span><\/a><\/p>\n
References<\/h1>\n