Decomposing a database's structure

talexb has asked for the wisdom of the Perl Monks concerning the following question:

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.

Alex / talexb / Toronto

"Groklaw is the open-source mentality applied to legal research" ~ Linus Torvalds

Comment on Decomposing a database's structure

Replies are listed 'Best First'.
Re: Decomposing a database's structure by CountZero (Bishop) on Dec 08, 2008 at 06:07 UTC
One of the many sub-modules of SQL::Translator? CountZero A program should be light and agile, its subroutines connected like a string of pearls. The spirit and intent of the program should be retained throughout. There should be neither too little or too much, neither needless loops nor useless variables, neither lack of structure nor overwhelming rigidity." - The Tao of Programming, 4.1 - Geoffrey James	[reply]
Re^2: Decomposing a database's structure by talexb (Chancellor) on Dec 08, 2008 at 16:39 UTC
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. Alex / talexb / Toronto "Groklaw is the open-source mentality applied to legal research" ~ Linus Torvalds	[reply]
Re^3: Decomposing a database's structure by planetscape (Chancellor) on Dec 08, 2008 at 17:09 UTC
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. HTH, planetscape	[reply]
Re: Decomposing a database's structure by jplindstrom (Monsignor) on Dec 08, 2008 at 11:44 UTC
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	[reply]