Database Structure viewer/printing

views:

129

answers:

+1 Q:

Database Structure viewer/printing

I have an SQL Server database, with over 60 tables in it. This database has plenty of relationships between these tables and while it's normalized, it's still complex to use because of the huge number of tables.

There are, of course, many tools that can show the database structure in some diagram. SQL Server itself is even able to create such diagrams. Right now, I prefer to use DatabaseSpy from Altova. But although this tool has a good visual display of the structure, it just can't print the structure that well. (Well, it can print the diagram but no descriptive text of the structure.)

So I'm looking for a tool for SQL Server that is able to create diagrams and make prints of those diagrams plus additional texts describing all fields and links.

+1 A:

What's the deficiency with SQL Server Database Diagrams?

In any case, my surprising recommendation would be to try Sparx Enterprise Architect. While not primarily a database tool, it can reverse-engineer a database into an ER model, then you can create one or more diagrams from that model. It has a nice "Add Related Elements" command that allows you to create a new diagram to focus on one entity; drag the one entity onto the diagram; then add just those other entities related to the first.

You can also customize the level of detail displayed on the diagrams, and print the diagrams as one or multiple pages.

Oh, and it does UML as well.

John Saunders 2009-06-16 12:59:19

The deficiency is that I want a print-out of the database as images and text. And I know EA which needs ODBC to connect to SQL Server. The reports it generates is okay but I would prefer something that much easier. EA is a big all-in-one product with way too much functions. Like a huge Swiss army-knife that's just too thick to fit in your pocket.

Workshop Alex 2009-06-16 13:22:14

"Images and text": what text? The DDL? In any case, I recommend you try this task with the current version, 7.5, which has better scripting capabilities. You might find you can more easily automate the unpleasant parts, like the ODBC DSN. I still think you'll want to create your diagrams "manually", in order to highlight individual areas of the database. I would also stay away from the report features, and stick with just the diagrams, and maybe the DDL.

John Saunders 2009-06-16 13:37:59

Text would be some documentation about field and index definitions and the references between the tables. (0..n, 1..n, 0..1, etc.) I'm hoping that I can explain a textual report better than the raw DDL scripts to someone who has no real technical knowledge. (At least, no SQL/DDL knowledge.) The report would be more important than the diagram, unless the diagram is clear enough.

Workshop Alex 2009-06-16 13:49:59

Sounds perfect for EA. OTOH, if you want non technical people to understand your database, then you don't want to show them ER diagrams. Use a tool like NORMA (http://www.ormfoundation.org/files/folders/norma_the_software/default.aspx), and show them your database at a conceptual level, including natural language verbalization of what your DB actually _means_. "Customer purchased Product on Date" works better than showing them Customer, Product and CustomerProductPurchase tables.

John Saunders 2009-06-16 14:11:04

+1 A:

Anything more than twenty or so tables is always going to be hard to diagram comprehensively and be useful/user friendly.

You should take a look at the following question asked today:

How to make E-R diagram with 500 tables? (SO)
How to make E-R diagram with 500 tables? (My answer)

The general opinion/consensus is to not attempt one big diagram, but to break the database down into multiple diagrams of smaller manageable groups of tables (for example based on functionality or some other relationship).

Kev

Kev 2009-06-16 13:30:03

In my case, about 40% of all tables are just for lookup/filtering purposes. They have a code and a text and not much more. They don't have to show in the diagram, as long as they're reported in text. The remaining tables are complex, but it won't be too cluttered up in a diagram. Considering how most tables are related to one another, it's not easy to break it up in multiple diagrams.

Workshop Alex 2009-06-16 13:54:53

Alex - I feel your pain. Our main production DB has ~150 tables which I broke down into loosely related collections of tables. It wasn't ideal but got me through.

Kev 2009-06-16 14:18:39

ansaurus

tags:

views:

answers:

Database Structure viewer/printing

related questions