#!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{$tablename}});
		}
	}
}

foreach my $key (keys %var_tables){
        #if you are like me and only what a specific type of column relation 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;