Selecting from multiple tables

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

Recently I needed to select from multiple tables and get the column names and set up a custom default column definition loosely based on the type. So I did:

foreach my $tablename (@tables) {

        my $sth = $dbh->prepare(qq{select * from $tablename limit 1});
+ 
        $sth->execute();
        my @fields = @{$sth->{NAME}};
        for (0 .. $#fields)
        {       
          $default_column{$fields[$_]} = $sth->{mysql_is_num}[
$_] ? 0 : '""';
        }
        $table_columns{$tablename} = \@fields; 

}
[download]

Is there a better way to do this, perhaps with using INFORMATION SCHEMA now available in MySQL 5, which I am using? Thanks monks.

Comment on Selecting from multiple tables Download Code

Replies are listed 'Best First'.
Re: Selecting from multiple tables by anonymized user 468275 (Curate) on Jul 20, 2005 at 11:19 UTC
The syntax of the mysql for returning columns of a specific table is `SHOW COLUMNS FROM tbl_name [FROM db_name] [LIKE wild]` [download] But if you want to get all columns from all tables in mysql (in this example with the table it was found in to the left): `SELECT table_name, column_name FROM information_schema.columns` [download] One world, one people	[reply] [d/l] [select]