There are several tools out there to make your life easier by creating an ERD for your existing db. Everything works pretty well when you have a small number of tables with FKs mapped, but when the number gets bigger, the diagram naturally gets a lot messier. Here are some of the ones I tried.
Free Tools
SQL Server Management Studio (SSMS)
It’s quite easy to use but can be messy for bigger diagrams. Also, the export options aren’t great if you are looking to have this live outside of SSMS, but, on the flip side, you can also store the diagram right there with your db.
Easy because it’s already in a tool you probably use.
It allows you to choose the tables you want in the diagram and then offers you options for formatting it.
Even without a ton of tables, though, it can be hard to follow the lines to where everything connects, but that’s kind of the nature of any diagramming tool – even the best of them.
Nicely, you can store it right with your db.
Azure Data Studio (ADS)
The only way to get a diagram in ADS is to use an extension, which requires you to add a .vsix file to your ADS. I was surprised by how usable and quite nice this extension was, especially when it warned, “This project has no serious testing.” I think this is a pretty solid free option.
Once you have it installed, you can get the Schema Visualization option when you right-click your db and choose Manage.
I feel like it’s comparable to the SSMS diagramming – at least on a small db.
And with this you can export it to SVG or MD file.
Here’s what the SVG file looks like.
And here’s what the MD file looks like.
Web Based Tools
Lucid
I found this one pretty nice to use. It doesn’t seem to automatically add the PK and FK references with lines. You need to import the SQL for it to generate the diagram, but unlike other websites mentioned below, this actually worked. It even gives you a query to get the schema from your db system. Choose File — Import Data to get started.
Choose Entity relationship (ERD) and Import Your Data.
Select your DBMS and copy the query to execute in your db.
You upload a SQL file with results from the query they provide you and click Import.
Then, you need to add the tables you want from a side panel on the site.
Then, get something like this as you add each table. Unfortunately, it doesn’t seem to automatically draw the line connecting PKs and FKs.
You need to create the relationships yourself. You will have to drag from one point on one table to another to create the relationship.
If you want to change that relationship line, you need to select it and then choose a dropdown near the top.
You will need to sign up for a free account to use it. You can have three editable documents at a time with the free plan. https://lucid.app
They have a nice tutorial video to walk you through an ERD using Lucid. https://www.youtube.com/watch?v=xsg9BDiwiJE
draw.io
This doesn’t have a way to import schema to make a db diagram, but I thought it’s a great free option if you want to create one from scratch. https://app.diagrams.net/
Other web ones that didn’t work well
Ones that didn’t work or work well for me – your mileage may vary. Mostly, they had errors for me, though.
https://app.quickdatabasediagrams.com
Tools That Aren’t Free
Dataedo
It needs a metadata db, and I didn’t want to create one or use an existing db for it, so skipped this one. It could be promising if you are into that metadata db.
https://dataedo.com/product/entity-relationship-diagram-tool
dbForge
I found this a better option as it didn’t need a separate db to use it. You need to use SQL login; otherwise, you need an application ID to connect with AD/extra creds. I just used SQL login to test it out. It’s pretty good. I liked it more than the free options, but then I didn’t have approval to expense it for work, so I kept with the free options.
https://www.devart.com/dbforge
DBeaver
I’ve used this in the past to connect to databases, but then I discovered they have ER diagram capability. Unfortunately, at the same time, I also discovered that this tool is no longer free.
https://dbeaver.com/docs/dbeaver/ER-Diagrams
The post ERD Your Existing Databases appeared first on sqlkitty.