Free software to reverse engineer existing complex database diagrams in SQL Server 2008+

Question

Up until a few years ago I used Microsoft Visio to reverse engineer an existing database to a diagram. This feature has been deprecated for a while now, and I'm rather stuck in finding an alternative.

Requirements

The FAQ for this new SE site instructs me to be precise in the requirements, so I'll give my best. Most of these requirements were in Visio, though not all of them. I guess finding one piece of software that has them all is not feasible, but I'd like to get close.

Important:

• SQL Server 2012 databases;

• Reverse engineer tables with all columns and Primary/Foreign Key indications;

• Reverse engineer foreign keys as connectors between tables;

• Manual layouting of boxes and connectors (I want to group stuff visually);

• Basic theming: choose background colors for tables;

• Able to handle complex models, e.g. 100+ tables;

• Able to choose which items to show or not (e.g. leave out certain tables, etc);

• At least a free trial available;

• Exporting to various formats for distributing and printing purposes, most preferred is PDF, PNG would also do I guess;

• WYSIWYG;

Main bonus points:

• Runs on Windows 8 (though I'd install a Linux VM for software fulfilling all important requirements)

• Automatic layouting and connector routing to minimize overlap;

• Able to do SQL Server 2008 and 2008R2;

• Able to reverse engineer views;

• Free (or a free version);

• Incremental update, e.g. reverse engineer again and have tables added/removed, etc., whilst keeping theme, existing layout, etc.

• Pretty diagrams (modern look and feel, anti-aliasing, choice of fonts, colors, gradients, drop shadows, theming, etc);

• Friendly learning curve (I can handle many complex features as long as it's easy to get the basics done quickly);

MUST HAVE:

• Installer doesn't secretly install toolbars in my browser or change my home page! ;-)

My Research

Of course this question has been asked before, even on sister sites (with some success even though they're off topic there). Here's a few:

• Good tool to visualise database schema? [closed] (MySQL, 91 upvotes)

• Anyone know of a decent free DB schema reverse engineering tool? [closed] (MySQL, 6 upvotes)

• Generate an E-R Diagram by reverse-engineering a database (all DBMses, 10 upvotes)

And slightly similar, from DBA.SE:

• How do you document your databases? (sql-server, 103 upvotes)

Furthermore, Wikipedia has a table comparing several software packages for "reverse engineering" feature:

• Comparison of database tools, Features - visual design and reverse engineering

Tried

These are the things I've tried in passing:

• Visio 2003 (this is what I used to use). Not free, outdated, but otherwise it fit the bill for most points. Not an option anymore though.

• Visio 2010 and 2013 (this is what I have currently). Reverse engineering is no longer supported. I've considered writing a plugin or using VBA to hack things together though. Not sure how tough that would be. It's not free (except I guess that I personally have it already), but fits the bill on a lot of other points.

• Graphviz. Tried this quite some time ago, from what I remember it had a bit steep learning curve and I wasn't quite able to manually determine final layout.

• SSMS diagrams. This works for small cases, but can be cumbersome for larger cases.

• Visual Studio 2012 ADO.NET Entity Data Model. Manual layouting of connectors is limited, theming does not exist at all, it is really meant for a goal other than documentation and it shows (e.g. default you get "Navigation Properties", etc), and it doesn't work well for 100+ tables. This may be useful for smaller scenario's but doesn't fit the bill for me.

And I think I may have tried a few suggestions from the SO threads, though I can't really remember specifics.

So: what would you recommend?

Answer 1

Here's a recommendation I ended up using (after a lot more research and trying many different options):

Use plain Visio for this.

Here's why.

Explanation of this approach:

There is no tool that conveniently satisfies all mentioned requirements, not even one that fulfills the main requirements only. Visual Studio 2012 comes close, but lacks theming support and didn't work very well for a large number of tables.

The following process worked for me:

1. Create a blank Visio Flowchart¹ template. Choose a design you like. Set the page and print sizes to something you want (I chose landscape A3 format and the "Linear" theme).

2. Open a query² in SSMS on the side to get the details you'll need later: tables, column names, and IS NULL info.

3. Start adding the tables one by one. Choose manually which tables and columns you which to include.

This is an example of what the end result looks like:

Or a zoomed out screenshot of the entire model (because it was manual labor I chose to include significantly less than all 100+ tables):

The procedure I used to make the "tables" look as they do:

• Headers are bold and a larger font size.
• Separators aren't lines but just a series of bold — dashes after eachother.
• Four "sections": TableName, Primary Key, Foreign Keys, plain columns.
• Bold for NOT NULL columns.
• White for TableName and separators, black for column names.

In addition, I chose for foreign keys to:

• Never allow crossing connectors, and instead creating small "reference" blocks that refer to a table that's somewhere on the other side of the model.
• Not include all tables (i.e. there are more foreign keys than connectors).

Hopefully the above will be of use to others too.

Requirements from the question:

As a bottom line, I do recommend this approach, mainly because you end up with a visually appealing model and a high degree of control. This approach doesn't fit the entire bill though. Here's how it holds up to the requirements:

Data connectivity:

• SQL Server 2012 databases
• Reverse engineer tables with all columns and Primary/Foreign Key indications
• Reverse engineer foreign keys as connectors between tables

± This is all possible but not automated. So "more or less".

Control and visuals:

• Manual layouting, basic theming, able to choose which items to show
• Able to handle complex models, e.g. 100+ tables

✓ Yes, this all works very well with plain Visio.

Moneys:

• At least a free trial available

✓ AFAIK there's a free Office trial available. I haven't looked into this though as I have Visio.

Other:

• Exporting to various formats
• WYSIWYG;

✓ Yes, this is available in Visio.

Bonus Points:

It has about half of the bonus points:

• runs on Windows 8
• SQL 2008 also supported
• pretty
• friendly learning curve

---

^{1. I've also tried the UML template and shapes but this has infuriating behavior for connector routing. Not recommended!}

^{2. This was my query:}

  SELECT  t.name TableName,
          c.name ColumnName,
          CASE WHEN c.is_nullable = 1 THEN '' ELSE 'BOLD' END AS IsNotNullable
  FROM    sys.tables AS t
          JOIN sys.columns AS c ON t.object_id = c.object_id
  WHERE   c.name NOT IN ('Excluded1', 'Excluded2')
  ORDER BY t.name,
          CASE WHEN c.name = t.name + 'Id' THEN 0
               WHEN c.name LIKE '%Id' THEN 1
               ELSE 2
          END,
          c.column_id

Answer 2

SchemaCrawler is a free, open-source database discovery tool that can reverse engineer your existing database.

Important:

• SQL Server 2012 databases - SchemaCrawler works with SQL Server 2012 databases
• Reverse engineer tables with all columns and Primary/Foreign Key indications - SchemaCrawler will create diagrams with all columns and foreign keys
• Reverse engineer foreign keys as connectors between tables SchemaCrawler will create diagrams with all columns and foreign keys
• Manual layouting of boxes and connectors (I want to group stuff visually) - SchemaCrawler can generate GraphViz DOT out put also, and you can use another tool to fine-tune layout
• Basic theming: choose background colors for tables - SchemaCrawler can generate GraphViz DOT out put also, and you can use another tool to fine-tune layout
• Able to handle complex models, e.g. 100+ tables - SchemaCrawler can handle a large number of tables - even better, you can supply regular expression search criteria to create multiple diagrams, of subsets of your schema
• Able to choose which items to show or not (e.g. leave out certain tables, etc) - SchemaCrawler allows you to supply regular expression search criteria to create multiple diagrams, of subsets of your schema
• At least a free trial available - SchemaCrawler is open source and free
• Exporting to various formats for distributing and printing purposes, most preferred is PDF, PNG would also do I guess - SchemaCrawler is open source and free
• WYSIWYG - SchemaCrawler is command-line

Main bonus points:

• Runs on Windows 8 (though I'd install a Linux VM for software fulfilling all important requirements) - SchemaCrawler is cross-platform
• Automatic layouting and connector routing to minimize overlap - SchemaCrawler does automatic layouts using GraphViz behind the scenes
• Able to do SQL Server 2008 and 2008R2 - SchemaCrawler supports all versions of SQL Server
• Able to reverse engineer views - SQL Server can reverse engineer views
• Free (or a free version) - SchemaCrawler is open source and free
• Incremental update, e.g. reverse engineer again and have tables added/removed, etc., whilst keeping theme, existing layout, etc. - Save your SchemaCrawler command-line command, and rerun
• Pretty diagrams (modern look and feel, anti-aliasing, choice of fonts, colors, gradients, drop shadows, theming, etc) - Some examples from the SchemaCrawler website
• Friendly learning curve (I can handle many complex features as long as it's easy to get the basics done quickly) _ Depends on you :-)

MUST HAVE:

• Installer doesn't secretly install toolbars in my browser or change my home page! ;-) - SchemaCrawler has no installer - unzip and run

Sualeh Fatehi, SchemaCrawler

Answer 3

Check out SQL Power Architect. It can connect to pretty much database that you can configure a JDBC connector for, and it can do all the reverse engineering that you are looking for. I only handles basic data types, though, so you have to convert things like DATETIME to DATE when you import them.

You can change the "Table Colour," but, in truth, that's really changing the color of the table's name/header than changing the background color for the whole table.

_{(source: sqlpower.ca)}

_{(source: sqlpower.ca)}

Answer 4

Visio 2016 now has an add-in for the reverse engineering

https://support.office.com/en-us/article/Reverse-engineer-an-existing-database-into-a-database-model-fb034862-acfc-45bc-88b2-f33d1e1f8614#ID0EAABAAA=Visio_Pro_for_Office_365

Answer 5

I just had the same requirements as the original question, but Visio already had an advantage for our choice as the ERD diagrams were once generated via reverse-engineering in 2007.

Since, we already have Office 365 subscription Visio Pro was added to it, which gave me Visio 16 (released end of 2015). Then, we installed Database Modeling add-in for Visio to reverse engineer our database.

I used the add-in to connect to a SQL Server (2019) database using ODBC driver (but it has more options to select driver and server like Oracle or SQL Server Native etc),

• Imported the schema,
• SQL Server 2012 databases - Yes (Tested with SQL Server 2019)
• Reverse engineer tables with all columns and Primary/Foreign Key indications - Yes
• Reverse engineer foreign keys as connectors between tables - Yes, including strong and weak
• Manual layouting of boxes and connectors (I want to group stuff visually) - Yes
• Basic theming: choose background colors for tables - Yes
• Able to handle complex models, e.g. 100+ tables - Yes, gives a warning but still imported and laid them out ok.
• Able to choose which items to show or not (e.g. leave out certain tables, etc) - Yes
• At least a free trial available - No
• Exporting to various formats for distributing and printing purposes, most preferred is PDF, PNG would also do I guess - Yes
• WYSIWYG - Yes

P.S: If I were to start, without any advantage/preference for Visio as mentioned above, I would likely choose dbdiagram.io

Answer 6

Your list of requirements is fully covered by Lucidchart. Well, it's not free, but it has a free trial.

With Lucidchart you run a query that they specify, then import the query results. This creates a collection of tables that you can drop onto a diagram like stencil shapes. Lucidchart draws the connections between the keys of the tables you've chosen. You can rearrange the tables and connections, remove fields, change colors and fonts, etc. As far as I can tell, you'd save 90% of the time required for hand drawing in newer versions of Visio, and you get most of the flexibility in formatting. And no worries about typos. The downside is that this isn't a live connection to your database, so to refresh you'll need to do some manual work.

You can export to various graphic formats (PNG, PDF, JPG) and to Visio (VDX).

Answer 7

Consider SQL Server Management Studio (SSMS).

Go to Database -> Database Diagrams -> Right click -> Add new diagram -> Add the desired tables for your ERD.

Pros:
Comes with SSMS, can extract and nicely display column names.
Cons:
Misses linked keys.

Note that none of the free or trialed tools listed above picked up linked keys for my db.

Answer 8

Try 
Apex SQL Model
Free: 
https://www.apexsql.com/sql-tools-model.aspx

with: 

Forward and reverse engineer
Customize the appearance of models
Create new or modify existing object groups
Navigate through model history
Export database models as images
Customize print output