How to generate a diagram of a very large database

Question

I have a very large database I need to diagram. The database is SQL Server 2008 on x64. It is large in that there are hundreds of related tables, each with up to 2000 fields (some are sparse), multiple relationships between tables (often hundreds per table, in fact), multiple schemas... you get the idea.

I tried to use the Database Diagrams feature of SQL Server Management Studio, but it crashed with a Win32Exception: "Not enough storage is available to process this command..."

I tried to use Visio's reverse engineering feature on a different machine to connect in and diagram it, but that's been going for a few hours with no sign of completion.

The scripts to build this giant schema are being by a tool we built for the job. While the tool is doing its job just fine, it's tricky to visualise its output.

I'm after a tool to kick out a diagram of this database so we can do this. Any suggestions?

EDIT: Just to emphasize, the diagram is indeed not supposed to be used for actual useful reference. It's a client relationship management device to demonstrate the complexity/scale of the system.

Answer 1

Use graphviz. Use some SQL statements to generate the digram, then run it through dot.exe to generate a PDF or PNG.

I've used it to generate digrams of data within SQL Server tables. No reason why you can use it for tables too.

http://www.graphviz.org/

There are also java, silverlight, and AJAX utilities for navigating extra large graphs, as PDF is only for one page.

Answer 2

I'd avoid doing the whole thing in a single diagram. As you mentioned, the tools crash, and it's probably not possible to easily comprehend a diagram with hundreds of tables with potentially thousands of records per table. Can you generate diagrams of smaller logical areas with some overlap to other logical areas?

Alternately, you could try using something like graphviz to parse the DDL statements and then produce a graph. It will probably churn for a while, but I remember seeing in a university poster-sized diagrams with tiny print, that were probably of the same complexity as yours. Good luck!