Beefy Boxes and Bandwidth Generously Provided by pair Networks
Do you know where your variables are?

List columns for a MySQL table

by EvanK (Chaplain)
on Jun 28, 2006 at 16:21 UTC ( #558057=snippet: print w/replies, xml ) Need Help??
Description: There seems to be no simple way to get a list of just columns in a MySQL table through the standard DBD::mysql module, so this snippet of code uses DBI's table_info method to do just that.

Given an existing database handle and a string matching a table name, it returns a simple array of column names. No muss, no fuss.

Of course, if someone knows a Better Way™, or sees an optimization for this code, by all means fire away!

EDIT: added use of map, cred to jhourcle
EDIT2: replaced use of column_info with a query which is about 40% more efficient. props to jZed for coming up with that.

# SYNTAX: getColumnList($dbh, "TestTable")
sub getColumnList
  # grab aliases to a DB handle and a table name
  my($dbh,$table) = @_;
  my $sth = $dbh->prepare("SELECT * FROM $table WHERE 1=0;");
  my @cols = @{$sth->{NAME}}; # or NAME_lc if needed

  # return now complete list of columns for the given table
  return @cols;
Replies are listed 'Best First'.
Re: List columns for a MySQL table
by jZed (Prior) on Jun 28, 2006 at 16:55 UTC
    Personally, I'd do this instead:
    my $sth = $dbh->prepare("SELECT * FROM $table WHERE 1=0"); $sth->execute; my @cols = @{$sth->{NAME_lc}}; $sth->finish;
      that'd certainly work as well :)

      it'll throw an error as is, though, you need a semicolon to end the SQL statement

      apparently, my server is strangely configured or something, as this isnt the norm.

      Build a man a fire, and he'll be warm for a day. Set a man on fire, and he'll be warm for the rest of his life.
      - Terry Pratchett

        Sorry, you're wrong. AFAIK, no DBI driver requires a semicolon at the end of a SQL statement and many will throw an error if you put one in (though DBD::mysql seems to allow, but not require it).

        update : To quote from the DBI docs: "Some command-line SQL tools use statement terminators, like a semicolon, to indicate the end of a statement. Such terminators should not normally be used with the DBI."

Re: List columns for a MySQL table
by jhourcle (Prior) on Jun 28, 2006 at 16:32 UTC
    # loop through ref and extract only the column name my @cols; foreach my $i (0..$#{$ref}) { push(@cols, $ref->[$i]->[3]); }

    Any time you're taking an array, and creating another array based on values from it, you should think about using map, rather than an initialize & push in a foreach loop:

    my @cols = map { $_->[3] } @$ref;
Re: List columns for a MySQL table
by dbwiz (Curate) on Jun 29, 2006 at 13:04 UTC

    If you want to get the columns from a MySQL table (not an arbitrary query), it's simpler, as shown in DBI Recipes, to use selectcol_arrayref combined with describe.

    my $col_names = $dbh->selectcol_arrayref( qq{describe $table} );
Re: List columns for a MySQL table
by davorg (Chancellor) on Jun 29, 2006 at 13:25 UTC

    Or, for a more portable approach, use the Catalogue methods that the DBI spec defines and that all DBDs should implement.

    Update: Ok. I see you originally used column_info and changed for performance reasons. If column_info is that much slower than raw SQL then I'd suggest that someone should raise a bug against DBD::mysql :-)


    "The first rule of Perl club is you do not talk about Perl club."
    -- Chip Salzenberg

      i dont think it was that column_info() itself was slower, but the processing required on the output of it to get an array of column names probably was...this was the original snippet (with jhourcle's improvement of using map()):
      my $sth = $dbh->column_info( undef, $db, $table, '%'); my $ref = $sth->fetchall_arrayref; my @cols = map { $_->[3] } @$ref;

      Build a man a fire, and he'll be warm for a day. Set a man on fire, and he'll be warm for the rest of his life.
      - Terry Pratchett

Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: snippet [id://558057]
and all is quiet...

How do I use this? | Other CB clients
Other Users?
Others making s'mores by the fire in the courtyard of the Monastery: (6)
As of 2018-04-19 14:31 GMT
Find Nodes?
    Voting Booth?