Not sure how useful this is but I have often found myself tasked with working on someone elses code that deals with a MySQL database on the back end. Given that foriegn keys and sub selects are not available to MySQL < 4.1 users (myself included) I came up with this script to find most of the major relations in a MySQL database. This script is meant to be ran from the command line and also requires a mysqldump text file to run. The goal is to supply a developer with all the tables that need to be accounted for from any of the other tables that are being manipulated at any given time. So if you are deleting from a table which other tables need to be accounted for if the delete is to clean out the system.
Here is an example:

$> mysqldump -d my_database > my_database_structure.sql
#the -d is to not include the data simply the table structure of the d
+atabase.
$> perl db_relation.pl my_database_structure.sql > my_database_relatio
+ns.txt
#the script will print to STDOUT unless directed to a textfile.
[download]

What is produced is a text file that will list each of the columns in the database and place an asterik next to the column in question with a description of the table that holds it.
Not rocket science I know but it has proven helpful when you need to see a quick snapshot of what is in your database and how it relates.

#!usr/bin/perl -w
use strict;
my $filename;
my $file_contents;
my %tbl_vars;
my %var_tables;

if(scalar @ARGV && $ARGV[0] =~ /\w+/){
    $filename = $ARGV[0];
    unless(open(FILE, $filename)){
        die "Unable to open file :$filename";
    }else{
        my @file_lines = <FILE>;
        close(FILE);
        $file_contents = join('', @file_lines); 
    }
}else{
    print "\nNo file was specified\n\n";
    exit;
}
my @tables = $file_contents =~ /(CREATE TABLE [^\;]+\;)/gs;

foreach(@tables){
    my ($tablename) = $_ =~ /CREATE TABLE ([^\(]+)\(/;
    chomp($tablename);
    my ($vars) = $_ =~ /\(([^\;]+)\) TYPE=\w+\;/s;
    my @vars = map{    $_ =~ s/^\s+//;
            ($_ =~ /[\w\d]+/ && $_ !~ /(?:PRIMARY|UNIQUE|FOREIGN )?KEY
+\s*\(/)?$_:undef;
            }split(/\n/, $vars);
    foreach my $var (@vars){
        if(defined $var && $var =~ /\w+/){
            my ($field) = $var =~ /^(\w+)/;
            push (@{$tbl_vars{$tablename}}, $field);
            push (@{$var_tables{$field}}, {$tablename =>\$tbl_vars{$ta
+blename}});
        }
    }
}

foreach my $key (keys %var_tables){
        #if you are like me and only what a specific type of column re
+lation modify the commented line below.
        #it is currently set to look for columns ending in _id.
    #unless (scalar @{$var_tables{$key}} > 1 && ($key =~ /_id/)){
    unless (scalar @{$var_tables{$key}} > 1){
        delete $var_tables{$key};
    }else{
        print "\nVariable $key is found in tables:\n";
        foreach my $table (@{$var_tables{$key}}){
            foreach my $tablename (keys %{$table}){
                print "\t$tablename\n";
                foreach my $fields (values %{$table}){
                    foreach my $column (@{${$fields}}){
                        if($column eq $key){
                            print "\t\t* $column\n";
                        }else{
                            print "\t\t$column\n";
                        }
                    }
                }
            }
        }
    }
}
exit;
[download]

-InjunJoel

---

---

Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!

Titles consisting of a single word are discouraged, and in most cases are disallowed outright.

Read Where should I post X? if you're not absolutely sure you're posting in the right place.

Please read these before you post! —

• How do I compose an effective node title?
• How do I post a question effectively?
• Markup in the Monastery

Posts may use any of the Perl Monks Approved HTML tags:

a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr

You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)

	For:		Use:
	&		&
	<		&lt;
	>		&gt;
	[		&#91;
	]		&#93;

Link using PerlMonks shortcuts! What shortcuts can I use for linking?

See Writeup Formatting Tips and other pages linked from there for more info.