my $columns = get_columns($dbh, $table);

print @$columns;


sub get_columns {
  my $dbh   = shift;
  my $table = shift;

  #SQL statement always gauranteed to return 0 rows,
  #but ALWAYS returns the column names.  Basicaly a
  #portable "DESC $table"
  my $statement = qq{
    SELECT *
      FROM $table
     WHERE 1 = 0
  };

  my $sth = $dbh->prepare($statement);
  $sth->execute;

  #Get the column names for the $table
  return $sth->FETCH('NAME_lc');
}
[download]

Let me explain some of the code above:

In terms of efficiency, this solution does not return ANY rows, since 1 can never equal 0, so it shouldn't use as many resources, as a query with a LIMIT 1 would.

I could have used $sth->FETCH('NAME'), but I read in the Perl DBI book that it's best to use NAME_lc, which forces a lower case to all the returned values. If you are porting between databases, you're code could be expecting mixed case from one database, and another could return everything in all CAPS or all lowercase. Not being prepared for this could cause many hours of hair pulling. Always better to be safe than sorry.

NAME_lc can be specified when using $sth->FETCH and $sth->fetchrow_hashref, and a few other DBI methods too.

As a side note, I am not sure if this is MySQL specific, but the order of the columns returned by this routine are consistent with the order the columns are in the table structure definition, as returned in MySQL by DESC $table. I wonder if this is true of other databases?

try Re: Field names of database table from DBI