Tools to visualize a database and understand the datamodel quickly

Question

I have several SQL Server 2005 databases ranging from 20 – 600 tables in an application and no documentation. I am looking for a database diagramming tool that is smart enough to pick tables that seem to be related to one entity (e.g., tables related to Patient, tables related to Orders) or one functionality (e.g., Patient Management, Order Management) and show them separately instead of drawing the entire database.

In the past, I have seen tables related to one piece of functionality represented in one color in the ER diagrams. In a well designed database, perhaps there will be multiple schemas that group tables related to one functionality together. But as all these tables are in one schema, and I want a tool that is smart enough to perhaps suggest which tables should go together under one schema. It won’t be perfect but perhaps it is intelligent enough to examine which tables should go together (for example based on relationships between them or based on which tables seem to be accessed together in the stored procs).

The bottom line is that I want to understand the data-model as quickly as possible. A tool called Schema Spy ( http://schemaspy.sourceforge.net/ ) seem to be headed in the right direction, but I was wondering if anyone knew better/more comprehensive tools.

Thanks.

Answer 1

Have you tried Visio at all? While it does not satisfy everything you asked for it can reverse engineer a database and make very appealing diagrams with a little work.

I have never used it to understand an existing database, but I have used it to explain databases I have created.

Answer 2

SchemaCrawler is a tool that can generate database diagrams, and exclude tables and columns using regular expressions. It can also find relationships between tables (which it calls weak associations), even when that relationship is not expressed in the schema as a foreign key. The combination of these two features should help a consultant in understanding a new schema quickly.