The following routine does a portable DESCRIBE $table, that should be database independant. If it's not, please let me know: 
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?

In reply to Re: DBD::mysql LISTFIELDS? by dkubb
in thread DBD::mysql LISTFIELDS? by Hrunting

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.