Decomposing a database's structure

[talexb]

I'm really interested in extracting information about a database structure from a production database in order to determine which tables are currently being used and/or are linked to other tables, and which tables are not being used and/or are isolated. Before I joyfully dive in and start writing code, is there a tool or module that does this already? (It's a rather large DB2 database.)

Something that draws pretty pictures based on the resulting information would be even cooler, but that certainly isn't a requirement.

[CountZero]

One of the many sub-modules of SQL::Translator?

[talexb]

I had a look at the documentation but couldn't see anything that suggested it could start with a database and come up with any sort of structure information. Did you have a specific module call in mind?

Friend monarch has kindly suggested Schema Spy, a Java tool, so I may try that out.

[planetscape]

I've used both:

• sqlt-diagram
• sqlt-graph

Update: Schema Spy looks way cool; you will also find some nodes on DB diagrams etc. under Database Stuff.

[jplindstrom]

Are all the relations specified properly with FK declarations in the DDL, or are you talking about dumping SQL queries during runtime to see what tables are actually being used and joined?

I've written a rather nice table and SP graphing tool that parses DDL. It supports Sybase/SQL Server and MySQL currently, I don't know how close that is to DB2 syntax.

It's not actually released yet, but I could throw a tarball your way if you like.

/J