I wrote this code recently - it reads the schema, and attempts to guess linkages based on field and table names.

Ignore/delete the CGI /print code. 


#------------------------------------------------------
sub Show_Tables_and_Identify_Linkages{
    my ($tableRef) = @_; #Tableref gets populated here
    #--- Show View and Table names/links -------
    my %Do_not_render = map {$_ => 1} qw|XP_PROC sqlite_sequence|;

    print start_table({-border=>undef, -width=>'75%', -align=>'CENTER'
+,-cellspacing=>0,
                    -cellpadding=>2, -bgcolor=>'SKYBLUE'}), "\n",
                    start_Tr;
    # -- Use DBI "table_info" to get Table/view, then dig and get Colu
+mn names --
    # Store obtained info into $tableref, and display table/view names
+ in html 
    my $count;
    my %stylemap = (table=>'SampleBlock ShowTable', view=>'SampleBlock
+ ShowView');
    my   $sth = $dbh->table_info(undef,undef,'%',"'TABLE','VIEW'" );#(
+$catalog, $schema, $table, $type ));
    while  (my $t = $sth->fetchrow_hashref() ){
        next if $Do_not_render{ $t->{TABLE_NAME} };
        $count++ % 4 == 0 and print end_Tr,"\n",start_Tr;
        param("NamedQuery", escape($t->{TABLE_NAME}));
        print  td( {-class=>$stylemap{ lc($t->{TABLE_TYPE}) } },
             a({-href=>url(-relative=>1,-query=>1), -class=>'ShowView'
+, -title=> $t->{TABLE_TYPE} . ' query'},
               $t->{TABLE_NAME}));
        my @primary_keys = $dbh->primary_key( @{$t}{qw|TABLE_CAT TABLE
+_SCHEM TABLE_NAME|});# $catalog, $schema, $table );
        s/"//g for @primary_keys; # Zap quotes
        $tableRef->{ $t->{TABLE_NAME}}{_TYPE}= $t->{TABLE_TYPE};
        $tableRef->{ $t->{TABLE_NAME}}{_TABLELC}= lc $t->{TABLE_NAME};
        my $colcount=0;
        my $colinfo = $dbh->column_info(@{$t}{qw|TABLE_CAT TABLE_SCHEM
+ TABLE_NAME|},'%' );#$catalog, $schema, $table, $column );
        while  (my $c = $colinfo->fetchrow_hashref() ){
            push @{ $tableRef->{$t->{TABLE_NAME}}{FIELDS}    },$c->{CO
+LUMN_NAME};
            push @{ $tableRef->{$t->{TABLE_NAME}}{FIELDSLC}  }, lc($c-
+>{COLUMN_NAME});
            $tableRef->{$t->{TABLE_NAME}}{ $c->{COLUMN_NAME} }{TYPE}= 
+$c->{TYPE_NAME};# INTEGER TEXT etc
            $tableRef->{$t->{TABLE_NAME}}{ $c->{COLUMN_NAME} }{PK}  = 
+grep {m/$c->{COLUMN_NAME}/} @primary_keys;  # Primary Key
       }
   }
   $sth->finish;
    print "\n",end_Tr, end_table,"\n";
    CGI::delete(qw|q NamedQuery|); # From parameter list - keep this c
+lean

    # Identify Table Linkages (Must be post-processed after ALL table/
+field names are obtained) -----
    # A Field named "xxxid' is assumed to be an FKEY to table xxx
    for my $t (keys %$tableRef){
      for my $f (@{ $tableRef->{$t} {FIELDS} } ){
        next if $tableRef->{$t}{$f}{PK}; # Cannot link Primary keys
        next unless my ($otherTable) = grep {$_ ne $t && $tableRef->{$
+_}{_TABLELC} . "id" eq lc($f) } keys %table;
        $tableRef->{$t}{$f}{LINKEDTABLE} = $otherTable;
        for my $fk( @{$tableRef->{$otherTable}{FIELDS}} ){
            next unless $tableRef->{$otherTable}{$fk}{PK}
                     or lc( $fk ) eq "id";
            $tableRef->{$t}{$f}{LINKEDFIELD} = $fk;
            push @{ $tableRef->{$otherTable}{LINKEDTABLES} }, [$t,$f, 
+$fk ];
            last;
        }
      }
    }
    
    
}

[download]

     Syntactic sugar causes cancer of the semicolon.        --Alan Perlis

In reply to Re: determining table structure in SQLite database by NetWallah
in thread determining table structure in SQLite database by Anonymous Monk

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • 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! —
  • 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:
    & &amp;
    < &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.